Database arhitecture

[Anon]

I don't know how to design my database. I would like to set up a sistem of storing data about courses that user attended. I already created a user table.

username, password, firstname, lastname, email

Now I don't know how to store users links(bookmarks). I'm considering three variations.

1. To make a table for each user where users courses will be stored

2. To make one table to store courses of all users and just add a field which will show who is the attended the course (who insert it).

3. To make one table for each year (1999, 2000, 2001) and than store data in appropriate table depending on the date when the course was held.

Can you make any suggestions?

Thanks
Blaz

[craigh]

1. add an ID field to Table1

2. use ID as a reference in Table2 containing:

ID
CourseName
YearOffered

you can then establish a one-to-many relationship (indirectly) between the tables by referencing the name and ID in the first table and then pulling the courses fromt he second table based on that ID.

[Anon]

There will be at least 5000 entries per year.
Is the number of records a problem? So, if I choose 2nd variation all records will be in one table. It can become big doesn't it.

I realy need to know how would you do it.

Thanx
Blaz

[kparker]

Close, but not good enough if courses are going to be attended by more than one user. In that case you need the following tables:

user (user_id, name, password, etc)
course (course_id, title, etc )
registration (course_id, user_id)

This last is a many-to-many table. Or you may even need the following, if courses are offered more than once, and it matters when the users took them:

user (user_id, name, password, etc)
course (course_id, title, etc )
session(session_id, course_id, date, location, etc)
registration (session_id, user_id)

[plutarck]

If you haven't read it already, here is a great article to read:

http://phpbuilder.net/columns/barry20000731.php3