RockStar RockStar - 1 year ago 75
SQL Question

How to create good database design using sql-server and asp.net?

Right now, I'm learning all about database design. Basically the contents of my database are brands for musical instruments. And there are two types of musical instruments which is Guitar and Bass. Now, I have stumbled upon a problem where sometimes a brand would have two types. Like for example in the table below, as you can see the ibanez is repeated twice so that it can accomodate both guitar and bass. I was advised to never repeat data in database but I haven't thought of a solution for this yet.

brandId  type        name      image

1         Guitar        Ibanez    xyz.jpg

2         Bass        Ibanez      hyz.jpg

3         Guitar        Fender    abc.jpg

4         Bass        Fender      fgh.jpg

5         Guitar        PRS        yui.jpg

6         Guitar        ESP        mbm.jpg

7         Bass        Warwick    omo.jpg

When i want to display the brand images in my webpage whether its for guitar or bass, i would normally use this query in asp.net.

public List<brand> GetBrandData()
{
MusicStoreDBEntities obj = new MusicStoreDBEntities();
List<brand> list = new List<brand>();
list = (from g in obj.brands where g.type=="Guitar" select g).ToList();

return list;

}


I wanted to know if there are any alternative database design approach where i don't need to repeat data. Kindly provide better solutions or better query in asp.net and hopefully with actual examples.

Answer Source

Basically What you'll want to do in order to avoid duplicates, is store your data in multiple tables instead of one.

It seems a bit silly with data this small, but what you'll need is a table Instrument, a table Type and a table Brand that are connected with primary/foreign key relationships like the following:

Database Model

And the data in your tables will look like this:

Data

The reason why you're doing this is consistency.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download