Best design? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best design?

Author

Topic

cipher
Starting Member

10 Posts

Posted - 2006-11-11 : 16:19:20

I'm currently building an ASP.Net application with SQL2005 that contains three types of users (Managers, Consultants and Admin) whose related data is stored in three simple tables:

CREATE TABLE dbo.Managers(
ManagerID int IDENTITY (1, 1) NOT NULL,
ManagerNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
...
) ON [PRIMARY]
GO

CREATE TABLE dbo.Consultants(
ConsultantID int IDENTITY (1, 1) NOT NULL,
ConsultantNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
...
) ON [PRIMARY]
GO

CREATE TABLE dbo.Admins(
AdminID int IDENTITY (1, 1) NOT NULL,
AdminNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
...
) ON [PRIMARY]
GO

Now, I need to add a diary/blogging page for each of these users and I'm currently thinking of creating one table, called Blogs, that will store this data. The only problem is when retrieving the associated data for a user I need a way to identify their group (ie Manager, Consultant, Adim) and then adjust the join to their related table. For example, this is the table I'm considering...

CREATE TABLE dbo.Blogs (
BlogID int IDENTITY (1, 1) NOT NULL ,
UserID int NOT NULL , --Associated ID
from either Managers, Consultants or Admins table
UserGroupID tinyint NOT NULL , --0=Managers,
1=Consultants, 2= Admins
BlogSubject varchar(50) NOT NULL ,
BlogMessage varchar(2000) NOT NULL
) ON [PRIMARY]
GO

Is this the best design decision, as far as performance and following SQL best practices goes, for achieving this functionality or would I be better of creating a Blogs table for each user (ie MangersBlog, ConsultantsBlog, AdminBlog) and then letting the middle tier determine the user type and then calling the appropriate data retrieval function?

Thanks in advance

pootle_flump

1064 Posts

Posted - 2006-11-11 : 17:49:21

SQL will not handle this design well (as you are finding) nor will referential integrity. As such performance & data consistency is likely to suffer. It would not be considered good database design in most circumstances.

What is wrong with a single Users table with a UserType column (accepting C for consultant, M for manager and A for admin or a surrogate since you appear to like them)? The relationship is ~~not~~ now simply UserID in Blogs referencing UserID in Users. You would also dump the UserGroupID from Blogs BTW.

EDIT - oops!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-12 : 04:24:49

I would create ONE table for the three types of users you are writing about.
Just take one of them and add either
1) add a new tinyint column UserType with values 0, 1 or 2 (for each usertype).
2) Add three bit columns named Manager, Consultant, Admin.

This makes querying much simpler and you won't get the hassle when there are some users sharing the same ID in the three usertype tables.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-12 : 12:23:00

You might need to have a "User" table, for the information that is generally Common across the three types of User, and then also the three tables Managers, Consultants and Admin for data which is unique to that type of person only. Those tables can have a PK of the same ID as the User table.

Kristen

cipher
Starting Member

10 Posts

Posted - 2006-11-17 : 20:52:07

Thanks for the responses. This really helped.

cipher
Starting Member

10 Posts

Posted - 2006-11-17 : 21:50:06

Another related question based on my updated design. Given the following new schema...

CREATE TABLE Users(
UserID uniqueidentifier NOT NULL ROWGUIDCOL DEFAULT NEWID(),
UserGroup_LovID uniqueidentifier NOT NULL,
FirstName varchar (30) NOT NULL
) ON [PRIMARY]
GO

Create Table ListOfValues(
LovID uniqueidentifier NOT NULL ROWGUIDCOL DEFAULT NEWID(),
LovType varchar(20) NOT NULL,
LovNum int NOT NULL,
LovValue varchar(30) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE ListOfValues WITH NOCHECK ADD
CONSTRAINT [ListOfValues_PK] PRIMARY KEY NONCLUSTERED (LovID),
CONSTRAINT [ListOfValues_NCI1] UNIQUE NONCLUSTERED (LovType, LovNum),
CONSTRAINT [ListOfValues_NCI2] UNIQUE NONCLUSTERED (LovType, LovValue)
ON [PRIMARY]
GO

ALTER TABLE Users WITH NOCHECK ADD
CONSTRAINT [Users_PK] PRIMARY KEY NONCLUSTERED (UserID) ON [PRIMARY],
CONSTRAINT [Users_LOV_FK1] FOREIGN KEY (UserGroup_LovID) REFERENCES ListOfValues (LovID)
GO

--insert demo LOV data
INSERT INTO ListOfValues(LovType,LovNum,LovValue) VALUES ('UserGroup', 0, 'Manager')
INSERT INTO ListOfValues(LovType,LovNum,LovValue) VALUES ('UserGroup', 1, 'Consultants')
INSERT INTO ListOfValues(LovType,LovNum,LovValue) VALUES ('UserGroup', 2, 'Admin')

INSERT INTO ListOfValues(LovType,LovNum,LovValue) VALUES ('PaySchedule', 0, 'Monthly')
INSERT INTO ListOfValues(LovType,LovNum,LovValue) VALUES ('PaySchedule', 1, 'Bi-Weekly')

...I'm now wondering about the Foreign Key join from the Users table to the ListOfValues table. Specifically, while the FK constraint is ensuring that the UserGroup_LovID exists in the ListOfValues table it doesn't contain any logic to ensure that is also links only on LovType = 'UserGroup'. Is this even possible when I'm defining the initial table structure or is this something I should be worryng about in the middle-tier?

Essentially, I want to make sure that there will never be Users.UserGroup_LovID value that links to a non 'UserGroup' record in the ListOfValues table.

Thanks again

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-17 : 23:33:24

1. I would not use uniqueidentifier for a pk column unless it's absolutely necessary. Do the ids really need to be globally unique? GUIDs are hard to read, take up more space, and will cause fragmentation if you decide to make your pk clustered (unless you use newsequentialid(), available on 2005 only). I would use int identity as you had in your first design.

2. I am not so sure about this "ListOfValues" table. It looks like you want to use it as a lookup table for every lookup value in your database. Overloading a table like that is not a good design. Better would be to have a UserType table (3 rows) and a PayScheduleType table (2 rows). A usertype is not a payschedule, and so they shouldn't be stored in the same table. This design would address your question as well, btw (EDIT: because User would link only to UserType, which has only user types in it, no payschedules).

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-11-17 : 23:43:02

foreign key takes care of your concern.

change LOV to roles, in name and in thought, it has proved a durable way to represent that idea.

"it's definitely useless and maybe harmful".

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-11-18 : 01:22:00

That last schema is terrible.

Go back to the other original idea with 3 blog tables.

Wrap a view around the 3 blog table and expose that to the middle tier...

DavidM

Production is just another testing cycle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources