• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Table Design Advice

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
SidFishes
Asked:
SidFishes
  • 6
  • 4
  • 3
  • +3
5 Solutions
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Patrick MatthewsCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Chris MangusDatabase AdministratorCommented:
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
 
HainKurtSr. System AnalystCommented:
also insert/delete some allergen would be much difficult if you with multiple column solution...
I just see headaches with multi column :)
0
 
HainKurtSr. System AnalystCommented:
experience talks :)
0
 
Richard QuadlingSenior Software DeveloperCommented:
I wonder if anyone will have a good reason to use the wrong structure?
0
 
SidFishesAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
_agx_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.
0
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
_agx_Commented:
True.  But I was actually grasping at straws trying to think of any reason to justify using that structure ;-)
0
 
_agx_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 ;)
0
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
@_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
 
_agx_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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now