Solved

Table Design Advice

Posted on 2011-09-02
16
383 Views
Last Modified: 2013-12-24
I have tblIngredients with a PK of IngredID

I'm adding a new one called tblAllergensPerIngredient with a pk of ApiID(int)

I have 2 options

I'd normally do a table like

ApiID(int) IngredID(int) AllergenID(int)

in conjunction with tblAllergens with a PK of AllergenID(int)

This is very flexible and expandable however given that the number of allergens is going to remain constant at ~10 I'm thinking the following would suffice

ApiID(int) IngredID(int) Allergen1(bit) Allergen2(bit) Allergen3(bit)...etc

Still thinking of going with the standard but throwing it out for comment
0
Comment
Question by:SidFishes
  • 6
  • 4
  • 3
  • +3
16 Comments
 
LVL 40

Accepted Solution

by:
RQuadling earned 200 total points
ID: 36474071
Table 1 - Ingredients.
Table 2 - Allergens.
Table 3 - Join table for Ingredients and Allergens.

Do NOT attempt to use an array-like structure. Your code will be horrendous.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 36474073
it will be difficult to work with multiple columns structure (suggestion 2)
I prefer first solution which is

ApiID(int) IngredID(int) AllergenID(int)


for example to find Api or Ingredient with some AllergenID would be musc easier with solution 1
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 36474075
IMHO, bad idea.  Suppose you need to query on which ingredients have a specified allergen.

In your 2nd option, you now have to interrogate 10 columns.  Not cool.

In a normalized approach, you only have to interrogate one column.

:)
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 100 total points
ID: 36474076
Even though it's only 10 allergens I would go with the child table.  If for any reason it ever extends beyond 10 allergens then you have additional design to do.

I can't count how many times over the years I've been told by a client, "we'll only have xx widgets, never more." and then a short time later there are more.

Build it with extensibility in mind.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36474077
also insert/delete some allergen would be much difficult if you with multiple column solution...
I just see headaches with multi column :)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36474087
experience talks :)
0
 
LVL 40

Assisted Solution

by:RQuadling
RQuadling earned 200 total points
ID: 36474103
I wonder if anyone will have a good reason to use the wrong structure?
0
 
LVL 36

Author Comment

by:SidFishes
ID: 36474137
ok ok I normally normalize :) and I'll do so again

I better close the q quickly before I get 300 posts telling what a bad idea it was to consider option 2 (hey it's early)

Thanks all
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 40

Expert Comment

by:RQuadling
ID: 36474180
Ha!

If you REALLY want to see what happens when a few of the experts get together, take a look at What software do I need to create a spreadsheet with hexagonal or octagonal cells?.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474183
(not for points...)

>> Still thinking of going with the standard but throwing it out for comment

     I'll bet you weren't surprised the answer was a resounding NO ;-)

>> In your 2nd option, you now have to interrogate 10 columns.  Not cool.

     As others said, it's just not worth the hassle.  I guess the 1 benefit is convenience of
     having all of the data in a single record. But everything else you'll need to do
     (searching, updating, aggregating, etc..) becomes more difficult.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36474210
If you need to put all the data in 1 row, you can in a view using multiple joins to the same table.

Still a messy way to go.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474258
True.  But I was actually grasping at straws trying to think of any reason to justify using that structure ;-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474291
>> If you REALLY want to see what happens when a few of the experts get together

@RQuadling - Where was that photo taken? How is it you were all together in the same spot? I don't know very little about the experts on EE other than some names are more familiar than others ;)
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36474305
Possibly the only time I would use something like that is if I inherited an app with it in and the redesign is more expensive to implement than to document and maintain the inefficient mechanism.

Or possibly where there is some real world limit to the size of the array.

But even then, it just means some complicated code when you need to search the array.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36474325
@_agx_ In San Luis Obispo, California. There was an EE Conference (there've been several) and some of us just happened to be sitting down and this daft question came up. I say daft. In the grand scheme of things, what happened next was more daft than the question, making true the old adage of "There's no such thing as a stupid question, just stupid answers!". And if I recall, there was a strange lack of beer involved. Just goes to show that the experts really DO care!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474726
@RQuadling - That's great . I didn't even know they had conferences.  

>> if I recall, there was a strange lack of beer involved.
>> Just goes to show that the experts really DO care!

Daft or not, that's what makes it so cool. Seeing others experts / geeks more into the question and solutions to a puzzle than beer.  Well .. for a while anyway.  It is a conference :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now