Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Designing


Table Designing

Author
Message
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3034 Visits: 5022
Hi Experts,

Can you please guide me on steps to take care while designing tables?

What all things me need to make sure while doing so.
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4520 Visits: 4558
A good start would be to read up on database normalisation
I would also ensure appropriate datatypes are used (e.g. store a date in a date field not text, a yes/no, true/false is stored in a bit field not an integer).
When you have some data, you can start looking at things in depth more like indexing, foreign keys and so on

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17645 Visits: 32268
All things you need to do when designing a database fills books. In fact, if you're just getting started at database design, I'd strongly recommend reading a bunch of books. It's way too easy to get this stuff wrong. I'd start with Louis Davidson. I love the way he teaches.

----------------------------------------------------
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
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3034 Visits: 5022
Thanks Stuart & Grant
Sachin Nandanwar
Sachin Nandanwar
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 2633
Make sure the Business Layer is ready.Never go for table design unless your business layer is not ready.That's my personal opinion.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1817
Set your own standards and stick to them.
I have a complex system that generates about 80% of the stored procedures (5000) so I can easily modify error handling for example and regenerate. This liberates me to spend quality time on what little custom code needs written.
My developers love everything I do below. It saves them from constantly looking up object definitions.
I'll include a sample list so you can get started and so everyone else can find a way to feel superior to me in some way.


Databases
Use the same logical names across a set of databases to simplify backups and restores. For example, give the logical name lims_data to a USALIMS_Development database and lims_log to its log file then use the same logical names for testing and production versions also.
Create a table called something like ApplicationSettings or DatabaseSettings and store a database structure version number which is updated every time a data definition language change is applied so you always know where each database is at structurewise.
Set the database to ANSI settings and rip out all ANSI settings from all object scripts.
is_ansi_null_default_on = 1
is_ansi_nulls_on=1
is_ansi_padding_on=1
is_ansi_warnings_on=1
is_arithabort_on=1
is_concat_null_yields_null_on=1
is_numeric_roundabort_on=0
is_quoted_identifier_on=1

Schemas
Think of schemas as folders in a database and all objects are in folders and no objects are in the root.
Microsoft has converted the owner dbo into a schema named dbo. I would recommend creating your own schemas and using them for controlling security permissions. Precede all object references with dbo or a custom schema name in all scripts.

Table Names
We use camel casing (word caps) such as AccessionPanel and AccessionNoteLog.
The names are generally singular nouns.
I would suggest Join tables be called <Table1>And<Table2>.
I would suggest Types tables be called <Table>Types or <Table>Lookup.
Avoid use of spaces or characters that are reserved words or require [quoted identifiers] syntax.

Types Tables
Types tables are used as lookup valid values.
I suggest adding a read-only column called InternalName so users are free to change a ShortName column without breaking code. For example a programmer may join to a QueueTypes table and filter on InternalName='On Hold' while a user may change ShortName to 'Awaiting Approval'. Alternately you could add a SystemLocked bit column.
I suggest adding a SortOrder column also which could easily be updated by most used or most recently used according to foreign key data.

Column Names
Column names are nouns and follow the same convention of camel casing such as AccessionKey.
Never abbreviate any object name and programmers will never have to guess at a name.
Matching column names across tables should always have the same data type and refer to the same concept.
The primary key is generally the first column in the table and is of type int and named the same as the table with the word Key appended, AddressKey for example.
Update: We are getting away from Identity primary keys and going to guids which are easier to insert in batches.
Our last five columns of every table are a standard set of auditing columns as follows: CreatedByUserName, CreatedDate, LastUpdatedByUserName, and LastUpdatedDate, and LastSaveID.
Foreign key columns are generally named the same as the primary key column they reference except when two foreign keys point to the same primary key such as ParentAccessionKey and ChildAccessionKey.

Primary Keys
Nearly every table should have a primary key.
These are named <TableName>_PK such as AccessGroupRelationshipPK

Foreign Keys
These are named <FKTableName>_<FKColumnName>_FK so they are always unique for example: AccessGroupRelationship_ChildAccessGroupKey_FK and AccessGroupRelationships_ParentAccessGroupKey_FK.
Decide up front if you will use cascade deletes and updates OR if you will write custom triggers to handle it, a mixture of those techniques gets confusing.

Alternate Keys
Nearly every table should have an alternate key to prevent duplicates from being inserted. It is a programmer's way of knowing when "where" criteria is unique.
These are named <TableName>_<ColumnName>_AK.

Column Defaults
We have very few column default values since stored procedures contain all columns as parameters.
They are named <TableName>_<ColumnName>_Dflt to guarantee name uniqueness.

Table Constraints
We generally have no unique indexes since these are all created behind the scenes as table constraints.
We have unique indexes when the index is filtered such as indexing the rows with a not null value.
The primary key and alternate key constraint naming conventions have been discussed.

Column Data Types
We have eliminated deprecated data types and inexact data types as part of Sql2008 Readiness..
We don't use Text or Image and have substituted Varchar and Varbinary for these.
We don't use Money or SmallMoney and have substituted decimal(p,s) for these.
We have eliminated the use of Float or Double and substituted decimal(p,s) where it makes sense.
We use DateTime columns when we need to store time with dates.
We use SmallDateTime as an indicator that the Time will always be set to 00:00:00. These will be converted to Date data type when upgrading to SqlServer 2008.
Define each table column as not-null as a rule. Drop back to nullable only if there is a strong business case for it. Nullability adds complexity and bugs.
Date range columns should be explicit in range such as BeginEffectiveDateOn and EndEffectiveDateBefore to show which operators should be used such as >, >=, <, <=.

User Data Types and Rules
We have eliminated the use of these as they only added confusion without adding any advantages.

Indexes
All unfiltered unique indexes have been implemented as table constraints.
All remaining indexes are named <TableName>_<ColumnName1>_<ColumnName2>_IE where IE stands for Inversion Entry (duplicate values expected).
Indexes are overhead for inserts and updates and deletes so create an index only after it become a performance problem. Not every foreign key column needs indexed.

Triggers
All triggers are named <TableName>_<Action> where action is usually Insert, Update, Delete, InsteadOfDelete, TemplateAuditInsert, TemplateAuditDelete, etc.

Stored Procedures
Standard stored procedures generally reference one table for its selects, inserts, updates, deletes, and processing. Use standard naming conventions for these such as <TableName>Insert, <TableName>SelectSingle, or <TableName>SelectByFK
Always use @<ColumnName> as parameter names; this allows the DBA to make sure parameter data types match changing column data types, a common bug in evolving environments.
Custom procedures that reference many tables should be named <Process><Action> such as InvoiceValidating.
Avoid cursors completely if possible. Write set oriented instead of procedural SQL as much as possible.

Functions
Currently we have no conventions though it may be useful to distinguish between scalar and table functions such as <Function>Tbl.

Security
For new development, use schema level security. It is much easier than granting execute on each procedure.
Use WindowsGroups as logins/users so network guys can manage security by job roles.

Synonyms
Use synonyms to point to any objects outside of the database. This allows simple redirection when other databases are renamed or moved.
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
Bill Talada (7/2/2014)
Set your own standards and stick to them....

I agree with that! Everything else (trying to promote your standards over others) just opens up a can of worms and eventually leads to the terms ISO and ANSI being bandied around! ;-)
Also, once you've settled on your standards, document them.

Regards
Lempster
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45428 Visits: 39942
Lempster (7/2/2014)
Bill Talada (7/2/2014)
Set your own standards and stick to them....

I agree with that! Everything else (trying to promote your standards over others) just opens up a can of worms and eventually leads to the terms ISO and ANSI being bandied around! ;-)
Also, once you've settled on your standards, document them.

Regards
Lempster


Heh... of course the standards actually need to be right. I've worked with many a couple whose main problem was the screwball standards that they came up with.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16671 Visits: 17030
Bill Talada (7/2/2014)

Update: We are getting away from Identity primary keys and going to guids which are easier to insert in batches.


I hope you have also made these guids a NONclustered index. If not you will need to constantly be defragging these. It doesn't take very many rows of a clustered index on a guid column to exceed 99% fragmentation.

My recommendation would be to find another column to use as your clustered index.

--edit--

And unless this is a distributed system I just don't care for guids. They are exceptionally wide and a complete pita to work with. Debugging and such is very painful to deal with.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 11771
The most basic thing is to make sure you really understand the real-world entities that you are trying to model, and to make the tables as close as possible to what they represent.

When user calls something an “Order”, are they talking about something a customer ordered, a shipment, or something else? It may depend on which part of an organization you talk to.

Nothing is worse than realizing that the table you designed actually represents many conflicting real-world entities.
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