I want to create a database which, involving user information. I have three types of user, i.e. Staff, Customer and Owner. For me, there are two ways to create the database for this.

The first way is, I create separate table each for Staff, Customer, and Owner. The table names and attributes are as below:

Staff (SSN, FName, LName)
Customer (SSN, FName, LName)
Owner (SSN, FName, LName)

And the second way is, I create only one table with the added attribute "UserType" to differentiate different kind of users as below:

User (SSN, FName, LName, UserType)

I have got advice from many others and what they told me is that I cannot create new table as I like such as in First solution abovemetioned, rather they suggest me to use the second solution as above.

Of course I know that to have one table is much neater and easier to maintain if compared to many tables. But I have doubt about the performance if I would have only one table. Just imagine that all the user information disregarding Staff, Customer and Owner are to be placed in just one table rather in three separate tables (e.g. 10,000 rows of tuple mass up together). For me, I prefer second solution as I think that it is easier to maintain in long run and may keep up the performance of database.

However, I still hope that anyone can share me your idea on this matter, thank you.

Regards,
Chuah

We have tables that have 100,000 users. It all depends on your DB.

Listen to your friends and go with the 2nd solution.

I would prefer the second solution. The only advatage of the first is if you have a person who could be of more than one type, i.e. a customer and an owner. You could however have a single table of person details and a second table of types, then a table joining the two with the ID's of both the person and the type. If you're never going to have people in more than one category this would make things over complicated.



--

Joe (Microsoft MVP - XML)

Ok, so the second solution is better in this case.

 

Does "a person who could of more than one type" mean such this as well:

It is best to use first solution if, for example, Staff details which may further categorized to Temporary Staff and Permanent Staff?

Not really, you could still have different types for both these categories. The problem only arises when a person can be both a temporary meber of staff and a customer for instance. Now you will get duplicated data unless you go with the three table approach.

--

Joe (Microsoft MVP - XML)

Thanks a lot, Joe. Thanks for your valuable opinion. :)