June 8, 2011 at 4:56 am
Hi
I'm putting together a design for a database that involves different types of user. I've assumed that there will be one place for them to authenticate, hence I want there to be a single table to store login information. However the different types of user will require different information to be stored about them, will have relationships to different entities and so need to be stored separately.
I'm therefore looking at a design along the lines of this:
Login UserTypeA UserTypeB
----- --------- ---------
Username(PK) Username(PK,FK) Username(PK,FK)
Password UserTypeA- UserTypeB-
LastLoggedIn specific fields specific fields
etc ...
This is similar to other designs I've worked with in the past, but I was curious as to how other people deal with similar issues. After all, with this approach, you also need to specify the type of user in the Login table so the application knows what to do after the user has logged in - possibly with a trigger to ensure that the type must correspond with the other tables, and you probably also need a trigger or two to ensure that the same username doesn't exist in both the UserTypeA and UserTypeB tables.
So is this how everyone else does it, or is there some other clever way I haven't thought of?
Comments appreciated.
Duncan
June 9, 2011 at 3:01 am
This problem is bugging me, since I feel there has to be a better/more elegant way of solving it. It is basically a specific case of the general "how do you deal with inheritance in relational databases?" question.
Another example could be you have to store vehicles - some cars, some trucks, some buses, some aeroplanes. All have wheels, registered owners etc., but only road vehicles have VINs, only planes have wingspans (except in Bond films, where cars sometimes do), and so on. How do you store this information in a relational database, without repeating common data across multiple tables?
The answers I've found so far always seem to come back to using one common table, and two or more "inherited" tables that use the same primary key as the common table.
Any thoughts?
June 11, 2011 at 12:48 pm
One thing that I noticied was that you may want to consider a UserType Table as opposed to UserTypeA and UserTypeB tables and separate columns?
I assume that you have a User table?
Do you mean UserID or the UserName?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 1:54 am
Welsh Corgi (6/11/2011)
One thing that I noticied was that you may want to consider a UserType Table as opposed to UserTypeA and UserTypeB tables and separate columns?I assume that you have a User table?
Do you mean UserID or the UserName?
Thanks for replying.
I think I've created some confusion with my naming. The two tables would be better described as User(TypeA) and User(TypeB). They are the two user tables, holding information specific to each type of user - for example fields, foreign keys to other tables and so on.
I used UserName as a primary key in all tables, but UserID would work equally well.
Duncan
June 13, 2011 at 8:15 am
Duncan Pryde (6/13/2011)
I used UserName as a primary key in all tables, but UserID would work equally well.
Duncan
UserID would probably be the better option since it is possible to have a duplicate UserName.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 8:55 am
Welsh Corgi (6/13/2011)
Duncan Pryde (6/13/2011)
I used UserName as a primary key in all tables, but UserID would work equally well.
Duncan
UserID would probably be the better option since it is possible to have a duplicate UserName.
I wouldn't want it to be possible to have duplicate usernames, since if everyone signs in through one page, they need to be identifiable uniquely through their usernames. If I used UserID, I'd still have a unique constraint on username in the login table. Does that make sense?
June 16, 2011 at 1:00 pm
There are two ways to do this (three, but I don't think one counts).
One is to have a complete table for each entity describing all attributes. In your case, UserTypeA would contain all attributes of UserTypeA as well as all attributes of Login. UserTypeB would contain all attributes of UserTypeB as well as all attributes of Login.
The other is to do as you have done here.
Which one is better is often hard to determine up front. Each will make some kinds of queries easier, and others harder, but it can be hard to tell which is going to be better in any particular case over the long term.
If you go with the first design, you may have the problem of enforcing the uniqueness of a column across the union of two tables (the UserName column in your example)
If you go with the second design, it is common to add a "type" column to all tables in the design. In the "parent" table, which holds the common attributes, this type can be any valid type. In each child table, holding specific attributes, the column is constrained to one value, being the type of all rows in that table. Your foreign key to the parent then includes this type column (so UserName, Type in your example)
I normally use the second design with the type column. This design keeps all of your cardinality constraints intact.
June 17, 2011 at 4:05 am
allmhuran (6/16/2011)
There are two ways to do this (three, but I don't think one counts).One is to have a complete table for each entity describing all attributes. In your case, UserTypeA would contain all attributes of UserTypeA as well as all attributes of Login. UserTypeB would contain all attributes of UserTypeB as well as all attributes of Login.
The other is to do as you have done here.
Which one is better is often hard to determine up front. Each will make some kinds of queries easier, and others harder, but it can be hard to tell which is going to be better in any particular case over the long term.
If you go with the first design, you may have the problem of enforcing the uniqueness of a column across the union of two tables (the UserName column in your example)
If you go with the second design, it is common to add a "type" column to all tables in the design. In the "parent" table, which holds the common attributes, this type can be any valid type. In each child table, holding specific attributes, the column is constrained to one value, being the type of all rows in that table. Your foreign key to the parent then includes this type column (so UserName, Type in your example)
I normally use the second design with the type column. This design keeps all of your cardinality constraints intact.
Thanks for the useful input. The idea of the type column is particularly elegant, and not something I'd thought of before. I reckon that should solve my worries about the same user potentially existing in both UserTypeA and UserTypeB tables, which is perfect.
Cheers,
Duncan
June 19, 2011 at 2:35 pm
Duncan Pryde (6/13/2011)
Welsh Corgi (6/13/2011)
Duncan Pryde (6/13/2011)
I used UserName as a primary key in all tables, but UserID would work equally well.
Duncan
UserID would probably be the better option since it is possible to have a duplicate UserName.
I wouldn't want it to be possible to have duplicate usernames, since if everyone signs in through one page, they need to be identifiable uniquely through their usernames. If I used UserID, I'd still have a unique constraint on username in the login table. Does that make sense?
You can define a unique constraint on the User Name but if you more than one Jane Doe then you will not be able to enter more than one record.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 20, 2011 at 1:15 am
Welsh Corgi (6/19/2011)
Duncan Pryde (6/13/2011)
Welsh Corgi (6/13/2011)
Duncan Pryde (6/13/2011)
I used UserName as a primary key in all tables, but UserID would work equally well.
Duncan
UserID would probably be the better option since it is possible to have a duplicate UserName.
I wouldn't want it to be possible to have duplicate usernames, since if everyone signs in through one page, they need to be identifiable uniquely through their usernames. If I used UserID, I'd still have a unique constraint on username in the login table. Does that make sense?
You can define a unique constraint on the User Name but if you more than one Jane Doe then you will not be able to enter more than one record.
Ah, now I get it. There's been a bit of a misunderstanding - I always use Username as a synonym for Login, which obviously needs to be unique for the whole system. The user's actual name would be stored as separate fields. 😉
Duncan
June 20, 2011 at 6:09 am
What column is the primary key?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 21, 2011 at 1:40 am
Welsh Corgi (6/20/2011)
What column is the primary key?
UserName (which is the login name)
June 21, 2011 at 2:04 am
June 22, 2011 at 12:14 pm
Jayanth_Kurup (6/21/2011)
Would it be acceptable to store the details as belowUsername,
password ,
Lastloggedin,
usertype,
XML_userspecificdata
What is stored in the XML Data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply