First of all there a number of ways to design this database.
You will need to choose the one that you feel suites your needs best.
Here one possible design.
Because you have different type of users/accounts, it looks like
"ACCOUNT_TYPE" table is needed
Account_type_id - autoincrement, PK
Account_type - varchar(25). ( Values in this field are:
Because you have user information to store:
User_id - autoincrement, PK
Account_type_id - integer, FK (foreign key to ACCOUNT_TYPE)
Fields of personal/Business information to follow - (First, Last Name,
Because you have web site security info to store:
User_id - integer, FK (foreign key to USER_INFO)
Fields of security information to follow
You can grow the database as you add module, but as much as you can
foresee and design ahead it would be better
From: Sudheer Satyanarayana [mailto:sudheer.s@stripped]
Sent: Thursday, June 14, 2007 1:50 AM
Subject: Design Help Needed
I'm creating an application for my web site. I want help in designing
database tables. Currently I'm starting with user management system.
The web site would have these types of users
1. Customer account
1a. Individual account. This user would be an individual with username,
password, billing address, account security question, answer and few
1b. Business account. Each business account would have many users.
Currently I have not decided the number of users for this type of
account. It may be 10 users in the beginning. I want to keep an option
to increase the number of users for business accounts. The business
account will have, business name, billing address, account security
question, answer, and few other business details. Each user within the
account will have username, password, first name last name, mobile
number and other personal details.
2. Partner account. These are similar to 1b business account type.
3. Internal account. These are employee accounts. Each user will have
username, password, first name, last name, department, phone number and
few other fields.
Ideally how many tables should I create? What are the types of
keys(primary and foreign)
Other modules of the application I would be developing in the future
are, contact management, shopping cart, mailing lists, customer support,
I have MySQL 4.1 on the server. Hope my question is clear.
PS: I'm new to databases.
Thanks for the help,
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql