Is My Normalization Abnormal? - Please helps

Last Post 02 Jun 2006 04:56 AM by SQLUSA. 15 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
24 May 2006 08:26 AM
Just to get an idea if I'm taking my db schema too seriously and too far.

Setting me straight on this one, will help me for the other entities as well.

For example, I have two lookup tables:

Servers
ServerTypes

So, 'Servers' would have ServerID, ServerName, IPaddress, etc. (data is obvious)

'ServerTypes' would have ServerTypeID, ServerTypeName, ServerTypeAbrv, containing data:

1, SQL Server, SQL
2, Web Server, WEB
3, Exchange Server, EXC

Now, what is the proper way to form the relationship between the two tables. Obviously, each server would have a servertype.

The question is do I store the ServerTypeID in the Servers table, or do I form a junction table, ie 'ServerTypeServer' (better name?) with SrvTypSrvsID, and the two FKeys from the tables, ie ServerID and ServerTypeID here?

Please help! This concept would mean the world2me Super Thanks!
mwesch
New Member
New Member

--
24 May 2006 06:30 PM
Well, your answer lies in how you configure a server. If a server can serve one and only one function, then you have a 1-to-many relationship between server_type and server. However, if a server can serve multiple functions, then you have a many-to-many relationship between server_type and server.

A 1-to-many relationship wouldn't have a junction table. A many-to-many relationshjip would have a junction table. It depends on your business rules.
SQL_Jr
New Member
New Member

--
24 May 2006 07:35 PM
Thx. michael. So, I think in my case that each Server would have one type or function, its acceptable to have a column ServerTypeID in the Servers table? Is that the correct assumption?
Isn't normalization the elimination of redundant data?
mwesch
New Member
New Member

--
25 May 2006 03:29 AM
Yes, that is what you would want. And no you're not violating normalization rules. It is absolutely okay to have the primary key values from ServerType in the Server table. That is the relationship. What you don't want to do is eliminate the ServerType table and then have a field in the Server table with redundant values like "SQL Server", especially if there are a lot of attributes of ServerType.

In your case, it is arguable about what the primary key of ServerType should be. The fact that you have a ServerTypeAbrv that is 3-characters long, unique, and reasonably descriptive means that it could be the primary key for ServerType. In that case, you would have add a foreign key field to Server for ServerTypeAbrv.

One advantage to that would be that a simple query of the Server table would allow you to quickly see what types of servers you have (because the ServerTypeAbrv is descriptive). Otherwise, you will have always have to join to the ServerType table to pick up the more descriptive ServerTypeName. Although with proper indexing, either way is acceptable in terms of normalization or performance.
SQL_Jr
New Member
New Member

--
25 May 2006 06:07 AM
Just the details i needed- Thanks for your help!

Quick other question. If I have say an Activity table that has several columns like:

OpenedBy
OwnedBy
AssignedTo

And, all of the above basically come from the Users Code Table and would have a value based on UserID, how do I represent this relationship in an ERD? I don't need to create foreign key relations do I? I can still derive the UserName, by querying on those values? Right?
SQL_Jr
New Member
New Member

--
25 May 2006 07:22 AM
Also, my email is sql_jr@yahoo.com - would it be an imposition to ask if you could take a look-see at my final ERD once its done?

Or anyone willing, please contact me via email. THX!
mwesch
New Member
New Member

--
25 May 2006 04:31 PM
Each of those fields would be a foreign key to UserID, so there would be 3 separate relationships.

Users.UserID<--Activity.OpenedBy
Users.UserID<--Activity.OwnedBy
Users.UserID<--Activity.AssignedTo

Best practice would be to create a foreign key constraint, which would enforce the values in the Activity table.
cmdr_jpskywalker
New Member
New Member

--
26 May 2006 03:42 AM
SQLUSA
New Member
New Member

--
28 May 2006 01:54 AM
Sorry Michael, you are wrong on this.

You can't really name the UserID foreign key as OwnedBy. That spells TROUBLE BIG TIME.

If a database architect looks at your tables, how would he know that OpenedBY is the UserID.

The Primary key - Foreign key relationship must be obvious from the naming.

Also an automatic reverse engineering program would get confused.

You need tables Object, and OwnerShip junction table representing many to many relationship:

ObjectID,
UserID
CreateDate
ModifyDate
ModifierBy

The last 3 columns are optional ROW MAINTENANCE columns, unrelated to the design.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/ordertriplecrown/
The Best SQL Server 2005 Training in the World

mwesch
New Member
New Member

--
28 May 2006 03:52 AM
Well Mr. Toth, I would ask you how relevant is an answer, if it has no reference to the original question? I did not choose the field names. I was simply referring to the names that sql_jr declared in his original post.

Your suggestion misses the solution entirely and isn't very helpful.

(1) Does your ObectID refer to ActivityID?
(2) Can an Activity have more than one owner? My assumption was no.
(3) If you assumed yes, then what is your junction table doing? Are you proposing 3 junction tables - one for OpenedBy, one for OwnedBy, and another for AssignedTo? It's not clear by your post.
(4) Why confuse the issue by raising the topic of optional columns unrelated to the design

Finally, an automatic reverse engineering program would not get confused if the relationship were enforced with a foreign key constraint as I suggested.
SQL_Jr
New Member
New Member

--
29 May 2006 07:02 PM
hi, all:

Please don't argue on my account.

I was just curious to find out how to properly represent that the USER table is a lookup table that can obviously be used for the fields 'openedby', 'ownedby', and 'assignedto'. Specifically, I was using ER Design Studio, and it only let's me make one relationship representation from pkey to fkey. I think.

Anyway, one ACTIVITY record would have only one owner, but the OWNER can have many ACTIVITIES.

Dos this clear it up? Am I on the right track now? THX ALL!
SQLUSA
New Member
New Member

--
02 Jun 2006 01:22 AM
Sorry Michael, you are wrong again.

This is a database design forum and we ought to promote good design practices to newbies.

My point was theoritical: it is WRONG to name foreignkey TableAID as XYZID in a different table.

If it is a voluntary choice, it is a DISASTER!

If it is involuntary choice, it means that the table structure is not good, and it is a warning sign that you cannot foreign key TableAID as "TableAID" in a different table.

This kind of naming surely confuses automatic tools. It also confuses mere humans.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
mwesch
New Member
New Member

--
02 Jun 2006 03:30 AM
So you still haven't answered how you would model the three 1-to-many relationships without using different names for the foreign keys?
SwePeso
New Member
New Member

--
02 Jun 2006 03:49 AM
quote:

Originally posted by: mwesch
Each of those fields would be a foreign key to UserID, so there would be 3 separate relationships.

Users.UserID<--Activity.OpenedBy
Users.UserID<--Activity.OwnedBy
Users.UserID<--Activity.AssignedTo

Best practice would be to create a foreign key constraint, which would enforce the values in the Activity table.


In this case, I would create a junction table for AssignedTo, since often the assignee change depending on first line support, second line support and so on...
SQLUSA
New Member
New Member

--
02 Jun 2006 04:56 AM
OK chaps. We agree on Server and ServerType.

So the Activity table. First of all, I would need specs to really respond to this.

Here is an approximation:

Table User ( UserID.....)

Table Activity ( ActivityID, UserID, ...) where UseriID is the OWNER.

Table ActivityOpen( ActivityID, UserID... )

Table ActivityAssignedTo (ActivityID, UserID....)


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World



mwesch
New Member
New Member

--
02 Jun 2006 02:15 PM
So, I can agree with both those schemas, if the relationships are 1-to-many. For example, an activity could be assigned to more than 1 user at a time, or if an activity may be assigned to one user who could reassign to another user and you wanted to retain the assignment history. In the case of the latter, a DateAssigned field would be included in the ActivityAssignedTo table. Of course, same could be applied to activity owner if there could be multiple owners.

You are not authorized to post a reply.

Acceptable Use Policy