?
Solved

Table Design Advice

Posted on 2011-09-02
16
Medium Priority
?
437 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:
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 61

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 61

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 61

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Suggested Courses

807 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