Best Practices for Database Design
-
When you use a phrase like "it's more hypothesis and theory than law..." it sounds to me like you are equating the two; placing hypothesis and theory on one side of the equation and law on the other. If not, then I apologize for making an incorrect inferrence.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks, that makes my point that whether or not the Person table is in 1NF depends on the business rules.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
For those who want to know more about the 1NF statement by Adam Machanic here are a couple of related links
http://www.dbdebunk.com/page/page/1103802.htm
http://www.dbdebunk.com/page/page/622301.htm
http://www.dbdebunk.com/page/page/622318.htm
http://www.dmreview.com/editorial/newsletter_article.cfm?nl=dmdirect&articleId=5893
Sorry Adam must have been posting at the same time.
-
Dbdebunk is a great site for stuff like this. Also, required reading for any SQL Server DBA should be "Database Design for Mere Mortals" by Michael J. Hernandez.
/edit - darn tootin' HTML....
-
Good book.
-
Even 1NF is not universally agreed upon. For instance, take the following table, which represents a person and his or her children:
Person(s) ()
---------
Person_Id
Person_Name
Child_Name1
Child_Name2
Child_Name3
Child_Name4
Whether it's 1NF or not (I say not), everyone who knows anything would agree it's bad db design.
Create table Parent
(
ParentID int identity Not null Primary Key,
FirstName varchar(100)
--...)
Create table Child
(
ChildID int identity not Null Primary Key,
ParentID int Not null references Parent(ParentID),
FirstName varchar(100)
--...)
Or......
create table Person
(
PersonID int identity Not null Primary Key,
PPersonID int null references Person(PersonID),
FirstName varchar(100)
--...)
Signature is NULL
-
Generally some good points here, although like others, we do not use underscores in object names. Rather initial caps are used to differentiate between different words forming the object name.
One standard, I would disagree with is naming databases according to whether they are production, development etc. This leads to rescripting releases going into different environments and I am a firm believer of not having to change scripts once they have been built in the development environment.
-
Some readers in this discussion here mentioned "hating" underscores for object names. I do agree that initial caps is more readable, but there are circumstances that justify underscores. For example, if you need to do data sharing with other database platforms that are case sensitive -- e.g. Oracle. So, if you name a table "NameOfTable" -- in the MSSQL world this name is find and dandy, but if you need to read this table from an Oracle gateway link, you are going to make your life hell for the developer. That is where the uppercase names of objects and underscores to separate logical meaning within the objectname makes sense. So, instead of "NameOfTable" you would name the object "NAME_OF_TABLE".
-
Whaddya mean hard to understand?
IVC10100 = un-posted invoice header.
IVC10101 = un-posted invoice detail.
IVC30101 = posted invoice header.
IVC30102 = posted invoice detail.
SY00500 = posted invoice batch header.
etc....
How is that hard to undestand?
jg
-
Very interesting discussion! Hard to criticize an article that generates that much thought!!!
For what it's worth, I decided NOT to include any reference to the object type in the object name after I had a need to create a partitioned view for an existing table in SQL Server 2000. After splitting the table into multiple tables by the values in the partitioning column, I dropped the original table and gave its name to the view. By doing so, no existing application code required modification. This would have been awkward if the naming convention distinguished between tables and views.
-
I agree with Roger, the amount of chatter this article has prompted pretty well defines its success. Agree or not, it got us all thinking and that's why I come here.
Regarding some of the points made by the author, I generally agree but for a few small things. Whether its tables, views, triggers, stored procs or Databases themselves, I always APPEND the convention.
For example, for a State abbreviation "reference table"
I might use something like "stateAbbrev_ref". One of the more annoying things to come across is several hundred tables in a list that all start with "ref" or views all starting with "vw_". How in the world can you quickly jump down the list to the correct alpha if all the tables , views, et al have the same prefix .. total craziness.A second point I would handle differently is naming your databases after the environment they're in. So myDatabase_DEV or yourDatabase_PROD. This would require making changes to SQL job steps and DTS packages;(among other things) every time you rolled out an upgrade to the next step in the chain. So going from Development to QA or QA to UAT and then later on to production, you would have to possibly change the database reference in each job step and DTS package that existed for that SQL job running. If you had a SQL job with 20 steps and 6 DTS packages being called each with one or more database connections in them, you're in for a real treat the next time you do a rollout to production. More craziness.
-
I don't have the time to read 101 posts and I'm sure someone else has mentioned some of the things I'm going to say.
I don't agree with the naming convention this article has, but in your company there should be a naming convention that is logical (it's the developer in me). Personaly, I prefix tables with "tbl" and views with "vew", so for example an employee table would be "tblEmployee". Then if there any supporting tables to the employee table I would name it "tblEmployeeJobHistory".
As far as abvrs. go for table or column names, I hate them! I used them in the past but then three years latter I go back and make modifications I can't remember what the column was without looking at the documentation. Personaly I think using abbrevations is a way of being lazy; take for instance the column first name, I've seen "fname" in multiple places, but if you name the column "FirstName" it's only four extra characters and it's much clearer for new staff... One caviot though, I do use abvrs. for datatypes; Ex: tbl for Table, vew for View, etc...
Again, there should be a standard for your company and it needs to be followed and enforced!
That's my 2¢'s.
Imagination is more important than knowledge.– Albert Einstein
-
I myself use a standardized naming convention as well (standard for me).
In all cases I really avoid complex keystrokes (ie: _) I don't believe that you need _'s all over the place. That said I do use them in certain cases (never in field names). Primarily I use a single _ in table names so that I can preface the table name with a table type / class designation. This allows tables of the same type / class to be grouped in easily.
As an example all customer tables will be C_xxxx, Transaction Tables (such as financial transactions) will be T_xxxx. Lookup tables L_xxx.
Also I will CAP the first letter of each word, and CAP all letters of an abbreviation. As an example my Status table would be called L_Status.
The primary key for this table would be StatusID
I have a work-flow engine that I designed in SQL. This requires several tables. To give an example of its naming see the following:
SYS_WF - Work-Flow Table
WFID - The Work-FLowID
WFName - Short name for the work-flow
WFDesc - Long description
.... And so on
SYS_WFG - WOrk-Flow Group
WFGID - Work-Flow Group ID
WFGName - Short Name for Work-FLow Group
WFGDesc - Long Description
..... Ect
SYS_WFPermission - Permission table for Work-Flow
WFGPermissionID - I think you can figure this one out
.... Ect.
Anyway, there is some of the naming that I use. For things like Stored Procedures I typically start the name indicating the table set / process that it belongs to, then the action. As an example for the work-flow engine, to populate Work-Flow Group records in that table I might use a name like:
WFGAdd
Change records:
WFGUpdate
ect......
And so on.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
I share you opinion with regards to under score. They have no real use and they are hard to type, to me even more difficut to read and look messy.
Regarding the prefix tbl? Why? What does this do for you? I have seen this practice SOOOOO much and can not figure out why people need to prefix objects like Tables, Views. The object type is obvious when working with the database. To me this is like stating the plain to see. Then again I could be missing the boat here.
Regarding Field names; I strongly believe you should never use ID as a field name. First of all it is reserved. Secondly when working with a large Stored Procedure and you have ID called everywhere it becomes difficut to determine from which table a given ID is related to. I feel that an ID should be named such as: StatusID, TransactionID, CustomerID, ThisFieldNameIsWayToLongID (
) <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
+1 agreement with Grant on this. Naming conventions are overdone in public forums, and the title of this article is inappropriate. One minor aspect of the development activity hardly qualifies as "Best Practices for Database Design."
Viewing 15 posts - 91 through 105 (of 145 total)
You must be logged in to reply to this topic. Login to reply