Table Design Advice

SidFishes
SidFishes used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
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.
HainKurtSr. System Analyst
Commented:
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
Top Expert 2010
Commented:
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.

:)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Chris MangusDatabase Administrator
Commented:
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.
HainKurtSr. System Analyst

Commented:
also insert/delete some allergen would be much difficult if you with multiple column solution...
I just see headaches with multi column :)
HainKurtSr. System Analyst

Commented:
experience talks :)
Richard QuadlingSenior Software Developer
Commented:
I wonder if anyone will have a good reason to use the wrong structure?
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
Richard QuadlingSenior Software Developer

Commented:
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?.
Most Valuable Expert 2015

Commented:
(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.
Richard QuadlingSenior Software Developer

Commented:
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.
Most Valuable Expert 2015

Commented:
True.  But I was actually grasping at straws trying to think of any reason to justify using that structure ;-)
Most Valuable Expert 2015

Commented:
>> 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 ;)
Richard QuadlingSenior Software Developer

Commented:
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.
Richard QuadlingSenior Software Developer

Commented:
@_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!
Most Valuable Expert 2015

Commented:
@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 :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial