What should I do?

[ywenz]

Say I've got a main table with bunch of records of images in my DB. Each image is a picture of a person, two of the attributes are age category, and weight(ideal or not). Would it be better to put the age and weight combinations into another table and generate IDs for each possible combination and use that ID in the main images table? Or would it better to just put the age ID and weight ID in the main table? Thanks

 

[jrbarnett]

Ywenz

Database normalisation dictates that each record should relate to the value in the primary key, so it should all be in one table strictly speaking,
but if you have lots of images in your database, it may well slow down general browsing and in this case you may want to put a foreign key in another table with a 1:1 relationship to speed up browsing of text based information if viewing the image is not always necessary.

John

 

[sfvb]

I agree with jrbarnett. On a small database, it probably wouldn't make any difference where the images are stored, but on a medium or larger db, storing the images in a seperate table is the way to go.

I do have one comment though. I don't think storing age, or age categories is a very good idea. You should store the birthdate and calculate the age, or age category. If you already have the birthdate, calculating ages is easy. If not, how do you know which people had a birthday yesterday and now fall into a different age category today?

There are numerous problems that I can think of that can, and do, arise from storing age categories. I don't know how you're using the data, and maybe it makes pefect sense for your application. But, in general, storing ages or age categories on an individual, is a bad idea.

 

[ywenz]

We're not storing birthdays because we dont' really care about the exact age of the person, we only care about what age bracket he/she fall under, because of privacy issue some ppl might only give us a bracket they belong to. So with that in mind, would you say this is the only way to do it? My real question is actually, would denormalization in this case help to speed up queries? If we need to know the age category and weight category of each person, wouldnt it be faster to store that info directly in the record of that person? as opposed to storing age/weight pairs into another table and then storing each age/weight pair ID into the main table? Less joins will be performed upon query time.

 

[lespaul]

If I understand what you are trying to accomplish:

I would set it up to have your main table with a personID, age and weight. Have a secondary table with personid and image file. Then set up tables that define your age and weight categories (MinAge, MaxAge, CategoryName). Yes you will end up with more joins in the query, but if you ever have to change the category requirements, you only have to change them in the one table rather than for EVERY record.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[skiflyer]

There are a few considerations to be made given the details here though.

If you change the categories, some one will no longer accurately fit into the new category right?

Do you want to keep a history or do you just care what they weight and how old they are right now?

Can you be certain the categories will never change? (this is a tough question to answer, so be very thoughtful.. but if you know it's a 6 month project to gather X data, it may be reasonable.)

-Rob

 

[ywenz]

Yes, the category will never change, it's set by a national safety agency. So I'm thinking to just have age and weight IDs in the main table and have an age and weight table to map the IDs to their respective categories. So when querings for individual images, two joins of the IDs would give me the category they belong to.