Hello forums !!
I would like to know the idea regarding db design for the following case:
CASE:
There are three types of users(say Customer, Business User and Personnel)
They have some parts common like first name, last name, email etc. Users like business user and personnel
have login access and permission types.
I would like to know:
1> whether to design three tables for the three types of users with all the required fields.
2> or one table with extra field user_type (for common fields). and other table for login infos and permission types.
Which one is better and why ? I would like to know the opinions of the forumians.
database design help ??
Moderator: General Moderators
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: database design help ??
I would make one table for all users with fields which are common to all three types, a field for type and a unique id which is a primary key.
The rest depends to a degree on how you intend to use the database. Fields which are unique to one type usually would be best put in a separate table with external keys to the primary key of the main table. Even some of the fields which are common to all types might be in a separate table.
However the design really depends on usage. If there will be only a few of the users with the unused fields, I would probably tend to keep everything in one table. Empty fields of type VARCHAR or SMALLINT don't take up much space.
Space isn't the only consideration. There is also the question of how you are going to be querying the table(s). I would suggest a book on database design, since it isn't all that straightforward.
The rest depends to a degree on how you intend to use the database. Fields which are unique to one type usually would be best put in a separate table with external keys to the primary key of the main table. Even some of the fields which are common to all types might be in a separate table.
However the design really depends on usage. If there will be only a few of the users with the unused fields, I would probably tend to keep everything in one table. Empty fields of type VARCHAR or SMALLINT don't take up much space.
Space isn't the only consideration. There is also the question of how you are going to be querying the table(s). I would suggest a book on database design, since it isn't all that straightforward.