July 2, 2014 at 6:06 am
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.
July 2, 2014 at 6:34 am
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 [/url]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
July 2, 2014 at 6:45 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2014 at 7:30 am
Thanks Stuart & Grant
July 2, 2014 at 8:14 am
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
July 2, 2014 at 8:30 am
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.
July 2, 2014 at 9:21 am
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
July 2, 2014 at 10:53 am
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
Change is inevitable... Change for the better is not.
July 2, 2014 at 11:07 am
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 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/
July 2, 2014 at 11:18 am
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.
July 3, 2014 at 1:44 am
Sean Lange (7/2/2014)
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.
You are so right there, Sean. I've worked with a database that consisted almost entirely of GUIDS with little or no enforcement of foreign keys. It wasn't a distributed system, but it was a nightmare!
July 4, 2014 at 4:04 am
Bill Talada (7/2/2014)
Set your own standards and stick to them.
Thanks a lot Bill for that crisp and clear steps to be taken care. Thanks Again
July 4, 2014 at 4:06 am
Thanks Everyone for the input.
July 8, 2014 at 7:23 am
Hi Experts,
Below are the original design and modifications recommended by us. Do i need to add anything or did i miss anything?
--Original Table design
CREATE TABLE ASC
(
[ASCID] UNIQUEIDENTIFIER NOT NULL,
[MuthalID] UNIQUEIDENTIFIER NOT NULL,
[ConsumableTypeID] UNIQUEIDENTIFIER NOT NULL,
[RemainingYield] INT,
[ReOrderDate] DATETIME,
[Recommendation] NVARCHAR (50),
[CreatedDate] DATETIME NOT NULL,
[CreatedBy] UNIQUEIDENTIFIER NULL,
[ModifiedDate] DATETIME NOT NULL,
[ModifiedBy] UNIQUEIDENTIFIER NULL
)
--Recommendataion from our side.
1)Create a Clustered Index on ASCID.
2)Non Clustered Index on MuthalID.
3)Non Clustered Index on MuthalId,ConsumableTypeId
4)Change ASCID data type to INT (with Identity) to reduce the size of clustered index
TIA
July 8, 2014 at 7:39 am
Ratheesh.K.Nair (7/8/2014)
Hi Experts,Below are the original design and modifications recommended by us. Do i need to add anything or did i miss anything?
--Original Table design
CREATE TABLE ASC
(
[ASCID] UNIQUEIDENTIFIER NOT NULL,
[MuthalID] UNIQUEIDENTIFIER NOT NULL,
[ConsumableTypeID] UNIQUEIDENTIFIER NOT NULL,
[RemainingYield] INT,
[ReOrderDate] DATETIME,
[Recommendation] NVARCHAR (50),
[CreatedDate] DATETIME NOT NULL,
[CreatedBy] UNIQUEIDENTIFIER NULL,
[ModifiedDate] DATETIME NOT NULL,
[ModifiedBy] UNIQUEIDENTIFIER NULL
)
--Recommendataion from our side.
1)Create a Clustered Index on ASCID.
2)Non Clustered Index on MuthalID.
3)Non Clustered Index on MuthalId,ConsumableTypeId
4)Change ASCID data type to INT (with Identity) to reduce the size of clustered index
TIA
Since these are recommendations I assume this must be something that is not yet built? #1 is a horrible recommendation UNLESS they also implement #4. A clustered index on a guid is just plain awful. Since this is the design phase I would HIGHLY recommend you change the name of the table. ASC is a reserved word and you will constantly be fighting with it. Also, a table name should give you an idea of what it contains. This seems like an abbreviation, I would recommend using an actual name instead.
What about MuthalID and ConsumableTypeID. Both of these are also guids. From this structure I would assume those are foreign keys to another table. Do those tables also have these guids as clustered primary keys? How about CreatedBy? This also seems like another guid as a primary key. Can users be deleted from the system? Are they actual deletes or just soft deletes? Do you have the ability to permanently delete after a soft delete? If you use a guid here you either have to make it a foreign key (which will prevent permanent deletes) or use no foreign key and then you have no clue who performed the action once that user is deleted. All you would be left with is a meaningless guid.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply