how to struture my MYSQL database

[pagegen]

Hi guys

 

I am making a mysql (most uptodate version), php (v5) web application

 

I am making a system which allows multiple user types to login so we will have admin, user, affliates, and customers

 

I can create a table called members and enter all the records in there and have a field which determins what the user type is. The issue is some members i.e customers will have other fields like age, date of birth etc so they will be extra fields for admin, users and affliates

 

can someone please advice me on how they would do this

 

[sunfighter]

To save a lot of work later use one table to list the people on your server. Have a auto increment column for user_id, then columns for their name(s), user name(?), password, status(admin, user, affliates, and customers) ... more.....  Also make columns for the extra info needed for the different statuses and make them able to be NULL. Then an admin does not need info in those columns. When someone logs in it's easy to checked their name and pass and then get all other info needed for the site.

[pagegen]

I was thinking one table named 'members' which stores fields like username, password, name and a 2nd table which extends members and has all the fields I need like age, etc which will not be needed in members then I can do an INNER JOIN

 

so somet like this

 

members:

id | name | username | password | user_type_id

 

members_customers:

member_id | age | sex | address

 

what do you guys think

[sunfighter]

Guess you didn't like what I said. So one more time

 

Your last post requires two queries. A single table uses one query. Your choice, both work.

[pagegen]

Hi mate,

 

sorry didnt mean it that way, your idea is great and I will go with it, just wanted to share what I had in mind to see what you thought

 

 

 

Thank you

[sunfighter]

No problem pagegen. I also didn't mean to come across as use mine or else. I was just looking for some recognition, I guess and thought one query was better then two, but your don't need to use two. 

 

Using an inner join with your set up will work just as good as mine and will also be one command. If that structure makes more sense to you then use it.

[ignace]

I was thinking one table named 'members' which stores fields like username, password, name and a 2nd table which extends members and has all the fields I need like age, etc which will not be needed in members then I can do an INNER JOIN

 

so somet like this

 

members:

id | name | username | password | user_type_id

 

members_customers:

member_id | age | sex | address

 

what do you guys think

 

That you are missing a few tables No need though to separate age and sex from other member info.

 

members (member_id, group_id, member_firstname, member_lastname, member_username, member_password, member_age, member_sex)

addresses (member_id, address_id, address_type_id, address_street, address_number, ..)

address_types (address_type_id, address_type_name)

groups (group_id, group_name)