Woodburn2006 Posted September 18, 2006 Share Posted September 18, 2006 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? Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 18, 2006 Share Posted September 18, 2006 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.RegardsHuggie Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted September 18, 2006 Author Share Posted September 18, 2006 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? Quote Link to comment Share on other sites More sharing options...
Jenk Posted September 18, 2006 Share Posted September 18, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.