db structure

[Woodburn2006]

i am designing a member login program and am having problems working out how to lay out the DB. i have a maintenance option, where they fill in a form and it adds it to a db, would it be best to have 1 maintenance table with all of the members queries etc in it or a seperate table for each member? but i will need to be able to reply to the queries and have them stored too. so should i use a maintenance post table and then have a replies table, or should i have a table that has all the members and all of their posts in it?

[HuggieBear]

My advice would be to seperate based on functionality.

So have a user table that only stores user info, make sure it has and auto-incrementing ID field, you can use this as your primary key and use it to relate to other tables.

Then have a messages_in table that contains only received messages, a message_out table that contains only sent messages, these tables can be linked via a column so that replies can be tied up with their original message. etc. etc.

Regards
Huggie

[Woodburn2006]

ok thanks, do you think that there should be a msg _in and a message_out table for each member? or just the one table and use the member id to pull it out?

[Jenk]

Put all messages on one table, with To column and a From column. Then when searching for sent messages; you can select where from = username, and to, select where to like %username%
.

Database tables should be split where functionality differs. User tables contain user info only. Message tables, only messages info. Where you have information that applies to two (or more) tables, use a foreign key to the table that is relevant for that information.

Such as the messages will need to know who sent/received it. So use the user id of the user as a foreign key, then you can select the message from the messages table, then the user info from the users table where the id's match.. (in a JOIN statement.)

This is the process of Database normalisation.

Also, don't create a new table for every user. That would be like creating a whole new database for every user.