designing databases

[AHarvey]

hi all,

I am in the process of developing a online database so we can track our members warnings, mutes and bans for work.

I've already written the login page and setup the table to have different users and track their passwords in MD5, username, first_name, email and company (we are 1 company working as outsourced teams for 2 online companies so I need to develop one database that caters for each company).

And now I am stuck, designing the rest of it. Any good guides for designing databases?

I know I will need a tables for the following:

members
warnings
mutes
bans

x 2, one for each company, but its knowing what to put in them and how to make them link that i am struggling with.

For instance, the members one will need the following:

member_id: primary and auto_increment
member_name: their onscreen name
alias: members can change names so I will have to link them to previous ones using member_id.

I'll then use the member_id in the tables for warnings, mutes and bans to track what they have had and when.

Just unsure of myself and could do with reading a few different things to make sure I fully understand it.

 

[LiE]

You need to think about the relationships between the tables. Try to explain them in english e.g Each member can have more than 1 warning. This in turn will allow you to come up with a design.

http://woork.blogspot.com/2007/10/project-database-define-relationships.html

http://dev.mysql.com/workbench/

 

[brainchylde]

I know I will need a tables for the following:

members
warnings
mutes
bans

x 2, one for each company, but its knowing what to put in them and how to make them link that i am struggling with.

Why do you have one for each company? Why not include a company table and company_id against the member?

i.e.

members
member_id, company_id,<other member attributes>

company
company id,company_name

warnings, mutes, bans exactly the same

Or,if a member can be part of two companies, a company table, and then a company_members table.

company
company_id, company name

member
member_id, <other member attributes>

company_member
member_id, company_id