Database updates

[Anon]

Hi,

I am building a database where users can log in and edit/add information.

I can't figure out how to make the database because one of the fields is going to be variable. This field is called Research, and may contain Research1, Research2, or however many Research locations the user wants to add.

On screen it will look like this:

Research Location # 1
Research Location # 2
Research Location # 3
etc...

How do I store this in the database? Because some users might have 2 Research locations, others might have 20, and some might just have 1.

Any ideas?

Thanx,
Chris

[toma42]

do a child table with a reference column to the main table.

Tables with specific column names which represent a specific value are foolish (such as location_1, location_2)

[Anon]

Could you explain what you mean by child table? Im new to this whole database thing, and I'm trying to do my best to understand it.

Thanx
Chris

[pnoeric]

Chris, to expand on what Tom said... you will end up with two tables. If this is about users, and each user can have a variable number of research, the two tables will be like this:

user_info
user_id <- important: serial # assigned by DB
user_name
email_address
(etc)

user_research
related_user_id
research_name
research_details
(etc)

So when you go to look up a user and all their associated research, your code will do TWO things:

1. Fetch the user from the user_info table. Now you know their name, email address, etc, plus their very-important user ID number.

2. Fetch all rows from the user_research table WHERE related_user_id = the user_id you pulled from the user_info table in the previous step. See how this can return as many records as they have in the table?

To store info in the tables... just do the opposite ;-) Seriously, make the user an entry in the user_info table, then get their user_id and plug in each research piece into the second table, with the associated ID number on each row.

There's good article in the php builder archives on this very subject, called something like "normalizing your databases." Recommended reading ;-)

good luck,
Eric Mueller

[pnoeric]

just to clarify since the formatting at some spaces: the two tables I detailed are

user_info
...user_id <- important: serial # assigned by DB
...user_name
...email_address
...(etc)

user_research
...related_user_id
...research_name
...research_details
...(etc)

[Anon]

There are some great dbms intros available on the web, including some that use PHP--try looking on webmonkey.com and devshed.com.