SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Design


Table Design

Author
Message
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4820 Visits: 953

Dear Experts ,
Please share if there are any standard guidelines for designing the table objects , apart from Normalization , and Column domain values/ranges the data can accommodate max , the type of the column etc and yes the infrastructure . Apart from these is there anything that is significant during design. Thank you.


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129598 Visits: 18973
No "standard" guidelines, no, but here are a few things that I stick to. There will be exceptions to nearly all of them!

(1) Choose the clustered index key carefully - but do include one
(2) The table should have a primary key constraint
(3) Referential integrity should be enforced with foreign key constraints
(4) Use a consistent convention for naming columns, tables, constraints and so on
(5) Don't use spaces, hyphens or a host of other characters in object and column names. Avoid reserved keywords also
(6) Choose data types appropriately
(7) Make full use of CHECK and DEFAULT constraints.

I could go on - but I'll leave it to others to add to (or disagree with parts of) the list.

John
Bill Talada
Bill Talada
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10346 Visits: 2296
Primary keys should be non-human readable like a guid...at least in pure theory. I love them. Never use a natural key like an SSN because your life will become miserable; there are many SSN dupes and updates will kill you. And stay away from those pathetic identity columns. Use Sequences or newid() instead so you can generate primary keys from the app instead of RBAR identity inserts.

Demand alternate keys (unique constraints) for virtually every table. Some tables may have two or three alternate key groups of columns. If you don't get this right, development will suffer and you will be babysitting the database and cleaning up all the time and I will think you are masochistic.

Use positive logic names throughout such as IsShipped instead of negative such as IsNotShipped.

Always store base data and never calculated stuff like ExtendedCost. It is cheap to recalc on the fly or add computed columns or views.

Err on the side of making columns not null and force developers to request nullable if needed. Performance and simplicity suffer from too many nulls.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)

Group: General Forum Members
Points: 347794 Visits: 34124
I love all the other answers. I'm going to repeat a few for emphasis and a couple of extra points. Almost everything I write has exceptions. However, exceptions should be exceptional.

* You should have a clustered index on every single table.
* Clustered indexes should be, as much as possible, unique
* Every table should have a primary key
* Every foreign key must be enforced through the use of WITH CHECK
* 2016 and greater, Clustered Columnstore for analysis focused data instead of clustered index
* The primary key should be on data that never changes
* For all your nonclustered indexes, it's better if your clustered index never changes too.
* The clustered index should be on the most frequently used path to the data, this may not be a PK or AK
* Because the PK should never change, GUID or IDENTITY are usually the best bets
* You should have an alternate key (AK) because your PK should be generated, see above, so that it never changes (cascading updates and deletes are from satan).
* Data types are so important. Strings are strings. Dates are dates. Integers are integers, etc. Don't mix them, ever. It just leads to pain.

Most of all, don't get overly obsessed with all these suggestions. You're going to have compromises. We all do. Go into them with your eyes open so you understand what the choices you make will mean to the behavior of the system.


----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Bill Talada
Bill Talada
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10346 Visits: 2296
I forgot to mention the most important point of all, a point that I've been seeing in many systems books over and over again the past few years...

Do not optimize to specific application requirements. Do not design to make it easy for a developer to implement his screen by denormalizing a table. Do not implement anti-patterns. Tell developers "No" when they ask for special implementations.

Always optimize to the system. Use standard database design patterns. Doing this will decrease the amount of custom code you have to write by 50% or more. Doing this will allow developers to focus on business rules instead of deciphering some clever hack saving another 50% in work time.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848197 Visits: 46691
Bill Talada - Wednesday, November 22, 2017 9:14 AM
Primary keys should be non-human readable like a guid...at least in pure theory. I love them. Never use a natural key like an SSN because your life will become miserable; there are many SSN dupes and updates will kill you. And stay away from those pathetic identity columns. Use Sequences or newid() instead so you can generate primary keys from the app instead of RBAR identity inserts.

Demand alternate keys (unique constraints) for virtually every table. Some tables may have two or three alternate key groups of columns. If you don't get this right, development will suffer and you will be babysitting the database and cleaning up all the time and I will think you are masochistic.

Use positive logic names throughout such as IsShipped instead of negative such as IsNotShipped.

Always store base data and never calculated stuff like ExtendedCost. It is cheap to recalc on the fly or add computed columns or views.

Err on the side of making columns not null and force developers to request nullable if needed. Performance and simplicity suffer from too many nulls.


There's no need to have RBAR inserts in the presence of an IDENTITY column. And there are no duplicate SSNs issued by the SSA. If you run across some, then people are lying. And, no... SSNs are not and have not ever been reused by the SSA.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848197 Visits: 46691
Remember or discover that the Clustered Index can have more than one use (didn't say more than one key) and is not necessarily the panacea of performance that people would sometimes have you believe, especially on wide tables.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason A. Long
Jason A. Long
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18484 Visits: 7170
Grant Fritchey - Wednesday, November 22, 2017 9:57 AM
I love all the other answers. I'm going to repeat a few for emphasis and a couple of extra points. Almost everything I write has exceptions. However, exceptions should be exceptional.

* You should have a clustered index on every single table.
* Clustered indexes should be, as much as possible, unique
* Every table should have a primary key
* Every foreign key must be enforced through the use of WITH CHECK
* 2016 and greater, Clustered Columnstore for analysis focused data instead of clustered index
* The primary key should be on data that never changes
* For all your nonclustered indexes, it's better if your clustered index never changes too.
* The clustered index should be on the most frequently used path to the data, this may not be a PK or AK
* Because the PK should never change, GUID or IDENTITY are usually the best bets
* You should have an alternate key (AK) because your PK should be generated, see above, so that it never changes (cascading updates and deletes are from satan).
* Data types are so important. Strings are strings. Dates are dates. Integers are integers, etc. Don't mix them, ever. It just leads to pain.

Most of all, don't get overly obsessed with all these suggestions. You're going to have compromises. We all do. Go into them with your eyes open so you understand what the choices you make will mean to the behavior of the system.

Just a quick add to Grant's really good post...
* Because the PK should never change, GUID or IDENTITY are usually the best bets

IF you have to resort to using a GUID, for the love of all you hold dear, make sure use you define the column with NEWSEQUENTIALID and NOT NEWID().
This 1 small thing will help avoid the massive index fragmentation caused by inserting a completely random GUID into your indexes.

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)

Group: General Forum Members
Points: 380898 Visits: 42229
I would stay away from GUIDs as a primary key, especially if it gets defined as the clustered index. For me there needs to be a good reason for using GUIDs as primary key, such as replication.
I like synthetic keys that are invisible to the application. I also believe that a natural key as one of the alternate keys is valid. And if you can identify more than one alternate key that could be beneficial as well.

Don't create single column indexes on every column.
Try not to duplicate indexes. Apparently there may be times when a duplicate index is a good thing, but I haven't found them useful yet (Jeff, what say you).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)SSC Guru (380K reputation)

Group: General Forum Members
Points: 380898 Visits: 42229
Oh, yes, be sure to normalize your tables, at least 3NF.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search