?
Solved

Table Design Advice

Posted on 2011-09-02
16
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
16 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 800 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 57

Assisted Solution

by:HainKurt
HainKurt earned 400 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 400 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 57

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 57

Expert Comment

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

Assisted Solution

by:Richard Quadling
Richard Quadling earned 800 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
 
LVL 40

Expert Comment

by:Richard Quadling
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:Richard Quadling
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:Richard Quadling
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:Richard Quadling
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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