March 11, 2014 at 2:04 pm
I'm on a development team that inherited a project from an office that was closed. I'm not a DBA but I am working to become one.
One of the things noticed within this DB is the amount of normalization that was done.
In my opinion, they over did it for some tables.
Within this DB they added 3 columns to every table called
DataLastMaint
PersNbrLastMaint
AuditId
They've added triggers to every table for insert, update's to handle these 3 columns
Next, comes the primary keys.
I've built my own database's at home for applications I've written for fun and with all my tables I would create a primary key which was int and an identity column.
For example, they would have TableA with 3 columns, 6 when you add these other common rows in every table
The primary key would be the first column, which could be a varchar(4)
Then Table B has 5 columns, 8 when you add those other 3
It's primary key is built from the one column from Table A and one more additional column in Table B
Then Table C has 7 columns, 10, again add the other 3
It's primary key is built from the 2 columns in Table B plus another column in Table C
I see this pattern on some table's right up too 7 columns making up the primary key
Am I wrong in assuming this is a bad design?
Wouldn't this be a performance problem?
Wouldn't an identity column in Table A which is a FK in Table B
and then the identity column in Table B is a FK in Table C a better approach?
Thanks for any info
Joe
March 11, 2014 at 2:11 pm
joepacelli (3/11/2014)
I'm on a development team that inherited a project from an office that was closed. I'm not a DBA but I am working to become one.One of the things noticed within this DB is the amount of normalization that was done.
In my opinion, they over did it for some tables.
Within this DB they added 3 columns to every table called
DataLastMaint
PersNbrLastMaint
AuditId
They've added triggers to every table for insert, update's to handle these 3 columns
Next, comes the primary keys.
I've built my own database's at home for applications I've written for fun and with all my tables I would create a primary key which was int and an identity column.
For example, they would have TableA with 3 columns, 6 when you add these other common rows in every table
The primary key would be the first column, which could be a varchar(4)
Then Table B has 5 columns, 8 when you add those other 3
It's primary key is built from the one column from Table A and one more additional column in Table B
Then Table C has 7 columns, 10, again add the other 3
It's primary key is built from the 2 columns in Table B plus another column in Table C
I see this pattern on some table's right up too 7 columns making up the primary key
Am I wrong in assuming this is a bad design?
Wouldn't this be a performance problem?
Wouldn't an identity column in Table A which is a FK in Table B
and then the identity column in Table B is a FK in Table C a better approach?
Thanks for any info
Joe
It sounds like you have composite keys on many tables? Many people in the industry feel that using identity columns is lazy and you should use the natural keys whenever possible. It seems that whoever designed your system feels the same way. There is nothing wrong with either approach per se. I would think though that if you have composite keys that are 7 columns there is something not quite right there. That sounds a little like over-normalization in conjunction with natural keys. Hard to offer any solid advice or opinion though without seeing the actual table structures.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2014 at 5:22 pm
First blush, no, I wouldn't say it's a "bad" design.
But, if I see varchar as a primary key, I'm assuming natural keys. There are good reasons for this from both a design perspective and best practice... but, they can be somewhat performance problems, especially if those values are also the clustered index. But the compound keys, those actually are a pretty cool way to design, if done well. It clusters the data such that all inserts are based on the stack of keys. It does the same thing for retrievals. I've seen that approach absolutely scream. Of course, we were using integer columns, not big fat varchar columns, but still.
Overall, it sounds OK, but possibly sketchy. I'd need to see the actual tables, the indexes, and, most importantly, the queries running against it all to say for sure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2014 at 7:15 am
Grant Fritchey (3/11/2014)
First blush, no, I wouldn't say it's a "bad" design.But, if I see varchar as a primary key, I'm assuming natural keys. There are good reasons for this from both a design perspective and best practice... but, they can be somewhat performance problems, especially if those values are also the clustered index. But the compound keys, those actually are a pretty cool way to design, if done well. It clusters the data such that all inserts are based on the stack of keys. It does the same thing for retrievals. I've seen that approach absolutely scream. Of course, we were using integer columns, not big fat varchar columns, but still.
Overall, it sounds OK, but possibly sketchy. I'd need to see the actual tables, the indexes, and, most importantly, the queries running against it all to say for sure.
+1
In our data warehouse, composite natural keys were common.
I think you also gain more understanding of the data, as you are not just adding something unique.
And from a design perspective, revision and current record come into play.
March 12, 2014 at 7:50 am
joepacelli (3/11/2014)
...Next, comes the primary keys.
I've built my own database's at home for applications I've written for fun and with all my tables I would create a primary key which was int and an identity column.
For example, they would have TableA with 3 columns, 6 when you add these other common rows in every table
The primary key would be the first column, which could be a varchar(4)
Then Table B has 5 columns, 8 when you add those other 3
It's primary key is built from the one column from Table A and one more additional column in Table B
Then Table C has 7 columns, 10, again add the other 3
It's primary key is built from the 2 columns in Table B plus another column in Table C
I see this pattern on some table's right up too 7 columns making up the primary key
Am I wrong in assuming this is a bad design?
Wouldn't this be a performance problem?
Wouldn't an identity column in Table A which is a FK in Table B
and then the identity column in Table B is a FK in Table C a better approach?
Thanks for any info
Joe
Without getting into the arguments for and against the use of an IDENTITY value, don't confuse the use of an IDENTITY column as a PK compared to an IDENTITY column for the use of a Clustered Index.
A PK doesn't have to be a Clustered Index and the Clustered Index doesn't have to be the PK - this is often forgotten/overlooked/misunderstood IMHO.
I appreciate this may not answer your question, but hope it is useful in finding the answer(s) you seek.
March 12, 2014 at 8:58 am
But with this composite key approach as the primary_key, if your table structure changes in the future and you need to add this additional column to the primary key this complicates the upgrade process.
For example. Let's say we have 6 tables
TableA, TableB, TableC, TableD, TableE and TableF
TableA
MsgEventCD (PK, nvarchar(4), not null)
plus additional columns
TableB
MsgEventCD (PK, FK, nvarchar(4), not null)
TmplNbr(PK, bigint not null)
TableC
MsgEventCD (PK, FK, nvarchar(4), not null)
TmplNbr(PK, FK, bigint not null)
FmtNbr(PK, bigint not null)
TableD
MsgEventCD (PK, FK, nvarchar(4), not null)
TmplNbr(PK, FK, bigint not null)
FmtNbr(PK, FK, bigint not null)
LocalityCd(PK, FK, nvarchar(10), not null)
TableE
MsgEventCD (PK, FK, nvarchar(4), not null)
TmplNbr(PK, FK, bigint not null)
FmtNbr(PK, FK, bigint not null)
LocalityCd(PK, FK, nvarchar(10), not null)
DataObjAppllNbr(PK, FK, bigint not null)
DataElemApplNbr(PK, FK, bigint not null)
TableF
MsgEventCD (PK, FK, nvarchar(4), not null)
TmplNbr(PK, FK, bigint not null)
FmtNbr(PK, FK, bigint not null)
LocalityCd(PK, FK, nvarchar(10), not null)
DataObjAppllNbr(PK, FK, bigint not null)
DataElemApplNbr(PK, FK, bigint not null)
ValKey(PK, nvarchar(10), not null)
Now you need to modify say TableB and add another column which becomes part of the primary key.
This change will have a ripple effect all the way down to TableF
If you used Identity columns, and these where your PK
and you generated unique indexes based on these original PK's.
Then if this new column is added, you only need to update the unique index.
March 12, 2014 at 9:13 am
No argument, but usually, it's relatively rare to change primary keys once a system has been built. It certainly doesn't make the early design and build stages or 1.1 releases easy. Again, I can't say I'd go with a pure natural key approach, but this compound key method can, and does, work really well. I've got a really old article[/url] I wrote about it a while back. I think it's roughly applicable to what you're dealing with.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2014 at 9:19 am
And likewise...a FK does not have to link to a PK, it can equally link to a unique clustered index constraint...
From Technet
"...you can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in another, or the same, table. ..."
March 12, 2014 at 9:43 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply