One ID for every database column, how to do?

Question

I working on a food database, every food has a list of properties (fats, energy, vitamins, etc.)

These props are composed by 50 different columns of proteins, fat, carbohydrates, vitamins, elements, etc.. (they are a lot)
the number of columns could increase in the future, but not too much, 80 for extreme case
Each column needs an individual reference to one bibliography of a whole list from another table (needed to check if the value is reliable or not).

Consider the ids, should contain a number, a NULL val, or 0 for one specific exception reference (will point to another table)

I've though some solution, but they are very different eachothers, and I'm a rookie with db, so I have no idea about the best solution.

consider value_1 as proteins, value_2 as carbohydrates, etc..

The best (I hope) 2 alternatives I thought are:

(1) create one varchar(255?) column, with all 50 ids, so something like this:

column energy              (7.00)
column carbohydrates       (89.95)
column fats                (63.12)
column value_bil_ids       (165862,14861,816486) ## as a varchar
etc...

In this case, I can split it with "," to an array and check the ids, but I'm still worried about coding praticity... this could save too many columns, but I don't know how much could be pratical in order to scalability too. Principally, I thought this option usual for query optimization (I hope!)

(2) Simply using an additional id column for every value, so:

column energy                 (7.00)
column energy_bibl_id         (165862)
column carbohydrates          (89.95)
column carbohydrates_bibl_id  (14861)
column fats                   (63.12)
column fats_bibl_id           (816486)
etc...

It seems to be a weightful number of columns, but much clear then first, especially for the relation of any value column and his ID.

(3) Create a relational table behind values and bibliographies, so

table values
energy
carbohydrates
fats
value_id --> point to table values_and_bibliographies val_bib_id


table values_and_bibliographies
val_bib_id
energy_id        --> point to table bibliographies biblio_id
carbohydrates_id --> point to table bibliographies biblio_id
fats_id          --> point to table bibliographies biblio_id


table bibliographies
biblio_id
biblio_name
biblio_year

I don't know if these are the best solutions, and I shall be grateful if someone will help me to bring light on it!

Answer 1

Appending more columns to a table isn't recommended nor popular in the DB world, except with a NoSQL system.

Elaborate your intentions please :)

Answer 2

You need to normalize that table. What you are doing is madness and will cause you to loose hair. They are called relational databases so you can do what you want without adding of columns. You want to structure it so you add rows.

Please use real names and we can whip a schema out.

edit Good edit. #3 is getting close to a sane design. But you are still very unclear about what a bibliography is doing in a food schema! I think this is what you want. You can have a food and its components linked to a bibliography. I assume bibliography is like a recipe?

FOODS 
id name
1   broccoli
2   chicken

COMPONENTS
id name
1   carbs
2   fat
3   energy

BIBLIOGRAPHIES
id  name           year
1  chicken soup     1995


FOOD_COMPONENTS links foods to their components
id  food_id component_id bib_id  value
 1   1         1          1       25 grams
 2   1         2          1       13 onces

So to get data you use a join.

SELECT * from FOOD_COMPONENTS fc
 INNER JOIN COMPONENTS c on fc.component_id = c.id
 INNER JOIN FOODS f on fc.foods_id = f.id
 INNER JOIN BIBLIOGRAPHIES b on fc.bib_id = b.id
WHERE
 b.name = 'Chicken Soup'

Answer 3

You seriously need to consider redesiging your database structure - it isn't recommended to keep adding columns to a table when you want to store additional data that relates to it.

In a relational database you can relate tables to one another through the use of foreign keys. Since you want to store a bunch of values that relate to your data, create a new table (called values or whatever), and then use the id from your original table as a foreign key in your new table.

Such a design that you have proposed will make writing queries a major headache, not to mention the abundance of null values you will have in your table assuming you don't need to fill every column..

Answer 4

Why, for the love of $deity, are you doing this by columns? That way lies madness!

Decompose this table into rows, then put a column on each row. Without knowing more about what this is for and why it is like it is, it's hard to say more.

Answer 5

Here's one approach you could take to allow you to add attributes all day long without changing your schema:

Table: Food - each row is a food you're describing

• Id
• Name
• Description
• ...

Table: Attribute - each row is a numerical attribute that a food can have

• Id
• Name
• MinValue
• MaxValue
• Unit (probably a 'repeating group', so should technically be in its own table)

Table: Bibliography - i don't know what this is, but you do

• Id
• ...

Table: FoodAttribute - one record for each instance of a food having an attribute

• Food
• Attribute
• Bibliography
• Value

So you might have the following records

• Food #1 = Cheeseburger
• Attribute #1 = Fat (Unit = Grams)
• Bibliography #1 = whatever relates to cheeseburgers and fat

Then, if a cheeseburger has 30 grams of fat, there would be an entry in the FoodAttribute table with 1 in the Food column, 1 in the Attribute column, a 1 in the Bibliography column, and 30 in the Value column.

(Note, you may need some other mechanisms to deal with non-numeric attributes.)

Read about Data Modeling and Database Normalization for more info on how to approach these types of problems...

Answer 6

I re-read your question a number of times and I believe you are in fact attempting a relational schema and your concern is with the number of columns (you mention possibly 80) associated with a table. I assure you that 80 columns on a table is fine from a computational perspective. Your database can handle it. From a coding perspective, it may be high.

Proposed (1) Will fail when you want to add a column. You're effectively storing all your columns in a comma delimited single column. Bad.

I don't understand (2). It sounds the same as (3)

(3) is correct in spirit, but your example is muddled and unclear. Whittle your problem down to a simple case with five columsn or something and edit your question or post again.

In short, don't worry about number of columns right now. Low on the priority list.

Answer 7

If you have no need to form queries based on arbitrary key/value pairs you'd like to add to every record, you could in a pinch serialize()/unserialize() an associative array and put that into a single field