Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Table Designing Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2014 6:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 5:42 AM
Points: 2,634, Visits: 3,980
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.
Post #1588406
Posted Wednesday, July 2, 2014 6:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 3,143, Visits: 3,418
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
Post #1588412
Posted Wednesday, July 2, 2014 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 13,920, Visits: 28,314
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1588416
Posted Wednesday, July 2, 2014 7:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 5:42 AM
Points: 2,634, Visits: 3,980
Thanks Stuart & Grant
Post #1588449
Posted Wednesday, July 2, 2014 8:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Make sure the Business Layer is ready.Never go for table design unless your business layer is not ready.That's my personal opinion.
Post #1588481
Posted Wednesday, July 2, 2014 8:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 145, Visits: 931
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.
Post #1588492
Posted Wednesday, July 2, 2014 9:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:59 AM
Points: 2,126, Visits: 1,474
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
Post #1588520
Posted Wednesday, July 2, 2014 10:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1588559
Posted Wednesday, July 2, 2014 11:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,093, Visits: 12,570
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)
Post #1588567
Posted Wednesday, July 2, 2014 11:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 25, 2014 3:18 AM
Points: 3,108, Visits: 11,504
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.

Post #1588573
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse