Jump to content


Photo

db structure


  • Please log in to reply
3 replies to this topic

#1 Woodburn2006

Woodburn2006
  • Members
  • PipPipPip
  • Advanced Member
  • 214 posts

Posted 18 September 2006 - 09:15 AM

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?

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 18 September 2006 - 09:52 AM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 Woodburn2006

Woodburn2006
  • Members
  • PipPipPip
  • Advanced Member
  • 214 posts

Posted 18 September 2006 - 09:56 AM

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?

#4 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 18 September 2006 - 11:31 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users