List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:June 14 2007 1:10pm
Subject:RE: Design Help Needed
View as plain text  
Hi Sudheer,

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 table

Account_type_id - autoincrement, PK
Account_type - varchar(25). ( Values in this field are:
"Individual","Business","Partner","Internal")

Because you have user information to store:

USER_INFO table

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:

WEB_SITE_SECURITY_INFO table

User_id - integer, FK (foreign key to USER_INFO)
Security_question
Security_question_answer
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




Mikhail Berman

-----Original Message-----
From: Sudheer Satyanarayana [mailto:sudheer.s@stripped] 
Sent: Thursday, June 14, 2007 1:50 AM
To: mysql@stripped
Subject: Design Help Needed

Hi,

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 
more fields.
 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,

etc.

I have MySQL 4.1 on the server. Hope my question is clear.

PS: I'm new to databases.


Thanks for the help,
Sudheer. S
Binary Vibes



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Design Help NeededSudheer Satyanarayana14 Jun
  • Re: Design Help NeededMelvin Zamora14 Jun
  • RE: Design Help NeededMikhail Berman14 Jun
  • RE: Design Help NeededJerry Schwartz14 Jun