Check List for DB Design

Question

Post reply

Check List for DB Design

Rajesh kasturi

SSCarpal Tunnel

Points: 4007
More actions
September 29, 2008 at 7:08 am

#382882

Dear All,
I have an existing database, I have been asked to make sure the database is well designed.
I have to come up with some changes needs to be done for the existing database.
Please let me know the check list or process to give the suggestions.
Rajesh Kasturi

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

You should start by making sure that there are no redundant columns and that the database is normalised to at least 3NF

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 2

Hi steveb,

Thanks for your quick reply, Is there any thing else apart from redudent columns and 3NF

Rajesh Kasturi

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

Unless there's a very good reason for it, most tables should have a clustered index. Be sure that, even if the database is normalized, it has appropriate primary and foreign keys. If you're using artificial primary keys, be sure that the natural key has a unique constraint in place. Check for consistant naming conventions. It doesn't really matter how you name stuff, just make it consistent.

That's all that comes to mind immediately for simply looking at the database design. After that you're getting into performance tuning. Are the right indexes in place? Are there indexes that aren't used? Stored procs... views... etc.

"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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

There's a lot to check. Do you know what queries will be run? Are they efficient given your design? You have to look at the joins and information you pull back. Sometimes it makes sense for slight denormalizations, computed columns, etc.

Those are more advanced techniques, and not necessarily something you want to look at if you aren't sure.

For basic design, check for 3NF as a general rule and be sure you have indexed your keys for joins.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Oh, and files and file placement. Minimum, split the data and the log to two different drives. After that, it depends on the types of drives you're working with, the size of the database, tables & indexes, the type of access. All this could drive further files and filegroups to be useful. As a general rule, not hard & fast, I include a seperation between data & index storage by default on most of the databases I build. I also usually put a seperate filegroup for BLOB columns.

"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

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Rajesh kasturi (9/29/2008)
Hi steveb,
Thanks for your quick reply, Is there any thing else apart from redudent columns and 3NF

As the others have said there is really a lot of things that can be tuned, it depends on what the database is going to be used for and what sort of hardware you have.

Data types are important you need to check you have appropriate data types, for example varchar instead of char, only use Nvarchar where needed, Instead of TEXT use varchar(max).

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

Exactly right.

It also brings up nullability, which should be used as sparingly as you can.

"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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 8

I'll disagree slightly with Grant.

Split your backups from the log and data first if you can. If you lose the data and logs, you'll have your backups. Next, depending on how busy the system is, put logs on the next array.

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 9

Dear All,

Thanks a lot for all your suggestions. really all are useful.

Rajesh Kasturi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

To add to that, make sure that you have check constraints and unique constraints where applicable and that you are using appropriate data types.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vivien Xing SSChampion Points: 12297 More actions · Answer 11

Archiving and/or purging should be included at the design stage, if applicable.

Vivien Xing SSChampion Points: 12297 More actions · Answer 12

more to add on:

naming conventions; data dictionary; record column type/length fit into 8k page properly;