Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Database design general standards?

Author  Topic 

Goalie35
Yak Posting Veteran

81 Posts
Posted - 2006-01-03 : 11:46:40
Does anyone know of a tutorial on the web that provides info on how to properly setup tables & stored procedures? Some sort of general standard that most professional developers follow? I started working on a new project with someone today and many of his coding practices differ from mine, so I was just curious if there was some sort of general standard when it comes to setting up a database.

For example, lets say we have 2 tables: TBilling & TBillingBillCodes. The primary key in TBilling is "billID". TBillingBillCodes has a foreign key that links to "TBilling.billID". Now in regards to naming this foreign key field, I always call it the same name as the primary key that it links to, "billID" in this case.

My co-worker's theory is the prefix of every field should always relate to the table name. So in this instance, he uses "bbc" to precede every field name (i.e. bbcID as the primary key, bbcBillID as the foreign key field, etc).

It's not a huge deal or anything, but I just felt it would probably make both of our lives easier if we could setup all of our data by adhering to some form of general standard. That's all.

Thanks in advance.

-Goalie35

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-01-03 : 15:10:27
Prefixing column names with a table abbreviation would be considered redundant by most sql practicioners, me included.

rockmoose
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2006-01-03 : 15:53:14
There have been some good threads about this subject (I'll try to find one). I've seen a lot of different conventions but consistency is key. As far as the column name thing, it's very confusing when the same entity is called different things and when different entities are called the same thing. So I agree with rockmoose on that one.

If you want to be remembered long after you've moved on just use Sql key words to name your objects. I actually named a table [group] once and though I'm not there anymore, it still is

I've posted this before but here it is again:
set nocount on

create table [From] 
	([Select]	varchar(15)
	,[group by]	varchar(15)
	,[Where]	varchar(15)
	,[Having]	varchar(15))
GO

insert [From] values ('Select','From','Group by','Having')

Select [Select],[Group by]
[From],max([having])[Having]
From [From] 
Where [Where]='Group by' Group by [Select],[Group by]
Having max([Having])='Having'

GO
drop table [From]


Be One with the Optimizer
TG
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-04 : 07:35:30
"Prefixing column names with a table abbreviation would be considered redundant by most sql practicioners"

Not me. We do this, with a tip-of-the-hat to Hungarian Notation and defensive-programming, to reduce error rates.

In a table for "Customers" lets say we have a column to store "Country Code". We also have a [FK] table for "Country Code Lookup"

For us the table names would be something like "CUST_Customer" and "CTRY_CountryCode" with PK's "cust_ID" and "ctry_Code" [where, for us, "ID" implies numeric 1,2,3,4 style and "CODE" implies a user-choosen code].

In the CUST_Customer table we will use a column called "cust_ctry_Code" to hold the customer's country code.

And now when I write the JOIN I will have

SELECT *
FROM CUST_Customer
     JOIN CTRY_CountryCode
         ON ctry_Code = cust_ctry_Code

which gives instant feedback that the join is "sensible", so that if I accidentally type

         ON ctry_Code = cust_Name

the chances are I will spot that I've gone wrong before I have to actually try to debug why it isn't working.

Also, it makes all our columns database-unique, which also reduces ambiguity and subtle hard-to-find errors.

But its horses-for-courses of course, just not sure I would say that "most sql practicioners" is an accurate assessment.

"consistency is key"

I'll second that. I would recommend that Goalie35 researches the conventions that various people have posted, comes up with a sub/super-set, agrees it with colleagues, is prepared to adapt it as time passes and when the consensus view demands it (which may involve re-visiting and sorting out legacy code), but above all Goalie35 keeps the company's code consistent (which may involve peer-review of code)

"Select [Select],[Group by]
[From],max([having])[Having]
From [From]
Where [Where]='Group by' Group by [Select],[Group by]
Having max([Having])='Having'"

LOL. Not funny!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-01-04 : 08:03:26
what is it with your obsession with cyrilic and greek these days, arnold??

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-04 : 08:09:49
Arnold speaks Greek? Shucks! I thought it was geek ...

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2006-01-04 : 08:26:33
quote:
Originally posted by spirit1

what is it with your obsession with cyrilic and greek these days, arnold??

Sorry, I'd already deleted the post you referred to -- it basically suggested that you could get rid of the brackets around names like [select] and so forth by using non-latin letters that are indistinguishable -- I couldn't get the characters to post correctly through this forum software. Is there a secret way of posting HMTL character references, like #x03BF; so they don't get escaped, like that one just did?

So, no particular obsession, It's just that they're supported by most truetype fonts so you won't just get 'missing glyph' square boxes.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-04 : 08:47:19
"Is there a secret way of posting HMTL character references, like #x03BF; so they don't get escaped, like that one just did"

Post a SELECT statement?

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-01-04 : 09:23:28
quote:
Originally posted by Kristen

"Prefixing column names with a table abbreviation would be considered redundant by most sql practicioners"

Not me. We do this, with a tip-of-the-hat to Hungarian Notation and defensive-programming, to reduce error rates.

In a table for "Customers" lets say we have a column to store "Country Code". We also have a [FK] table for "Country Code Lookup"

For us the table names would be something like "CUST_Customer" and "CTRY_CountryCode" with PK's "cust_ID" and "ctry_Code" [where, for us, "ID" implies numeric 1,2,3,4 style and "CODE" implies a user-choosen code].

In the CUST_Customer table we will use a column called "cust_ctry_Code" to hold the customer's country code.

And now when I write the JOIN I will have

SELECT *
FROM CUST_Customer
     JOIN CTRY_CountryCode
         ON ctry_Code = cust_ctry_Code

which gives instant feedback that the join is "sensible", so that if I accidentally type

         ON ctry_Code = cust_Name

the chances are I will spot that I've gone wrong before I have to actually try to debug why it isn't working.

Also, it makes all our columns database-unique, which also reduces ambiguity and subtle hard-to-find errors.


I respectfully disagree with that completely ! :)

Every column in any SQL statement involving more than 1 table should *always* be fully qualified with the table it is coming from. Prefixing column names to make them unique makes people move away from this, and that can cause bad habits. If you prefix all references to your columns with the table names in any multi-table SELECT, you will never have ambiguity and you will need less aliasing later on; (i.e., should 1 stored proc return Cust_CustID while another returns Invoice_CustID ? They should both return CustID to be consistent.)

You mention that this is clear:

ON ctry_Code = cust_ctry_Code

But isn't this even clearer:

ON Cust.CtryCode = Country.CtryCode

??

The first is a *guideline* and means nothing to me unless I am familiar with your general rules. The second is a SQL standard and is part of the language and is clear no matter which standards or guidelines you are enforcing. I would stick with the global standard that is built into the language.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-04 : 13:01:02
Well, like all things, it isn't quite that simple in our case

I agree that

ON ctry_Code = cust_ctry_Code
and
ON Cust.CtryCode = Country.CtryCode

are equally as clear, and yours is "SQL Standard".

However, SELECT Cust.CtryCode arrives at the application with a column name of "CtryCode" so has become ambiguous (OK so in this example ctry_Code and cust_ctry_Code hold the same value, but our naming convention provides for a unique column name for other "Country Codes" within the same table, or in a different table). I could alias the column ... but that's a source of errors if I have to do it every time I use the column, or inconsistent if I only do it when there is ambiguity. But that isn't really the numb for me.

We implement end-to-end naming, so @VARS used to temporarily hold the value of a specific column, application variables (VBScript in our case), template-tags (embedded in HTML) all use our "composite" column names. This allows us to do "Where is cust_ctry_Code used" type stuff, as well as global find&replace should the schema ever change [that never happens, of course <fx:whistles>], but in general my finger-in-the-air view is that this has reduced the error rate by making it obvious where data is coming from at a glance - whatever part of the application / language / source code my guys happen to be looking at.

I don't disagree with you, I was just expressing what we do and as I have also known it used elsewhere I questioned "would be considered redundant by most sql practicioners".

Either way, Goalie35 will need to decide what coding standards his Coding Shop will adopt, as I proposed above.

Kristen
Go to Top of Page

knucklesandwich
Starting Member

1 Post
Posted - 2006-01-05 : 08:52:33
I have been involved with databases for more than 20 years and I have rarely encountered the use of table name prefixing of column names, but we could argue the 'most' pre-cursor all day. In the end as Kristen states, establishing a standard is the most important issue. It isn't wrong to prefix column names, so if it works.....
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-01-05 : 13:55:32
quote:
I questioned "would be considered redundant by most sql practicioners"

To make the claim correct I would have to add "I think that..."

I appreciate the beneficial effects that Kristen experiences from prefixing table abbreviations to column names.
However the benefits could stem from having a uniform, enforced naming convention, not the prefixing per se.

The table-name prefixing would in my mind mostly add overhead and confusion,
mainly because attributes from the same domain will have different names depending on which table they are located in.
That would really hurt my sense of order.

Agree with all others on the consistency aspect. That is key.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-06 : 04:56:01
Rocky I'm not advocating it, just suggesting that there are different horses for different courses.

Standards are standards, and all that, but I think Goalie35 should keep an open mind until the research is done, and "I'll take this bit, and throw that bit away" is done.

"That would really hurt my sense of order"

It makes the most sense, in our case, because so much of our code is mechanically generated and in lots of places a "table.column" notation is not supported (i.e. the "." dot would muck things up), and it isn't passed through the Column Header stuff of an ADO recordset [you only get the column name].

Actually I thought a bit more what Dr. Cross Join said:

"ON Cust.CtryCode = Country.CtryCode"

and I could would be interested to hear what people do. My thinking is along the lines of:

So already the Country Code has a "table" prefix "Ctry" making it "CtryCode". Lets assume we want "Active", "Name", "Description" and "InternationalDiallingCode" too.

The first three names I'm going to use in lots of tables. The last will possibly only be in the Country Code table.

I could just give my columns names like "ID", "Code", "Ref", "Name", "Desc" ... and allow duplicate names in different tables. Or I could prefix the common ones (i.e. all bar the "InternationalDiallingCode" column), with "Ctry".

Which is pretty much back to where I came in!

Now ... if I don't prefix "InternationalDiallingCode" a) its inconsistent and b) when I do need that column name in another table in the future I'm in a bit of a legacy-spot!

So ... how about the naming of columns using names like "ID"?

Obviously its fine:

ON Cust.CtryID = Country.ID
(Custonmer Country Code JOINS to the PK in the Country table)
or

ON OrderHeader.ID = OrderItem.ID
(Order Header and Order Items share an "Order Number" value)

but IME this leads to errors like

ON OrderHeader.ID = Customer.ID

(wrongly associating an Order number with a Customer number) and this is hard to spot, and will work erratically (assuming that both IDs are, say, INTs)

Its really this latter point that drove me towards the prefixing notation. I read Standards and stuff, but I'm not a good follower of them. That makes it hard for me to get a job elsewhere but I think makes efficiencies for the development of our application in our one-product-company.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-01-06 : 05:43:50
well we only prefix our id's
so for organizations it's org_id, for persons it's person_id, etc...
all others are normal names with no prefixes.
so it's t1.org_id = t2.org_id etc...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2006-01-06 : 07:46:00
>>and I could would be interested to hear what people do

we name our columns with an un-abreviated description with no underscores. I try to keep the name just long enough to understand what entity it represents. But for generic attributes like code, size, id we include enough to distinguish from other similar attributes.
ie: customerCode, underwearSize, and personid. I hate it when you have no idea what the column represents by the name alone (ie: code). for internal (usually INTs) IDs I use the ID suffix. For external, legacy, or pnemonic identifiers I use CODE suffix.

For t-sql code I use table aliases for all queries and prefix all columns with table alias even when it is not ambiguous. I used to use underscores between words but don't anymore, that's just personal preference, IMO. However, whichever way you go with underscores the whole database should be consistant. So clean JOIN criteria to me is when the columns have the exact same name.
ie: join personorganization po on po.organizationid = o.organizationid (and I use that order - ON <table I'm joining to> = <already listed table>)

Be One with the Optimizer
TG
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-06 : 09:24:22
"well we only prefix our id's "

So ... roughly speaking ... if its going to be in a JOIN then it gets a representative and probably-database-unique name, and if not then column names for common entities ("Name", "Description" etc.) will be used as-is.

"For external, legacy, or pnemonic identifiers I use CODE suffix"

On that point (if anyone is interested!!) we use:

ID = IDENTITY auto-increment number
GUID - uniqueidentifier
CODE - Some short-ish code that OUR user assigns - e.g. "GB" for a country code
REF - A normally slightly longer code that is for an external reference that we have no control over - e.g. the ID/CODE that some external 3rd party allocates to a record.

"For t-sql code I use table aliases for all queries and prefix all columns with table alias even when it is not ambiguous"

I've read that this helps to keep the query cached, but I can't say that my tests have actually been able to prove this

"join personorganization po on po.organizationid = o.organizationid (and I use that order - ON <table I'm joining to> = <already listed table>)"

I'm a great fan of that, although I more commonly see it the other way round.

My reasoning is:

If I have a table that has a PK with 3 columns (PKCol1, PKCol2, PKCol3) then usually these will be part of any JOIN. So if I do

JOIN MyTable T
    ON T.PKCol1 = X.SomeColumnName
   AND T.PKCol2 = X.Foo
   AND T.PKCol3 = X.SomeOtherLongColumnName

it is easier to spot which columns in "MyTable" I'm providing Join columns for. Whereas turning them round runs the risk that they are off the right side of the screen and I may miss an incorrectly specified column:

JOIN MyTable T
    ON X.SomeColumnName = T.PKCol1
   AND X.Foo = T.PKCol2
   AND X.SomeOtherLongColumnName = T.Col177658 -- Hehehe!!

of course I could align the right hand side of the "=" with TABs to that they were easy to check, but they would still be a long way to the right and thus not always "easily seen".

I'm at a bit of a loss why its " more ok" to prefix some columns but not others, rather to prefix them all - I'd be interested in your thoughts though.

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-01-07 : 09:13:15
>>and I could would be interested to hear what people do.

The important thing is to be consistent, almost every database that I have come to work with has had a different naming convention.
The ones that have been most easily understood and workable, are (usually) the ones where the consistency of the naming has been well-enforced.

Some personal preferences and opinions:
1. prefix tables with t, views with v, functions with fn, stored procedures with sp (not sp_ !!!)
I know this is often argued, but one thing I like is that reserved names can be "overcome" eg. table [group] is tgroup.
2. no underscores (with 3 exceptions!)
3. always alias the tables in queries, and qualify the column names with the alias. (ok, I can cheat on one table queries, but regret it every time i have to extend the query)
4. camelCasing for spProcs and fnFunctions, not for tables or column names which are all lower case.
5. JOIN order opposite to the one TG mentioned, (select ... from tleftside ls join trightside rs on ls.someid = rs.someid)
6. prefix generic attribute like id, code, date, time, name, description with explanatory qualifier. (useremail, logintime, countrycode, countrydesc)
7. ...id suggests integer key, ...guid uniqueidentifier key, ...code some short character abbrv. (what Kristen said )
8. metadata columns in most tables (at least regdate and optionally some others: reguser, changedate, changeuser, procedureid...)
9. Singular form for table names

underscore exception 1:
For large systems with many subjectareas, a 3 letter code can be used to group the objects per subjectarea.
tUsm_sometable, spUsm_SomeProc, fnUsm_SomeFunction, tRep_sometable (assuming the subjectareas User Managment and Reporting as an example)
underscore exception 2:
For partitioned data and historization (tuserlogin_2003, tuserlogin_2004, tuser_history)
underscore exception 3:
For M:M relationships; tstudent, tcourse, tstudent_course

rockmoose
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-01-07 : 09:43:46
interesting how many different conventions are there, no?

i only disagree with number 4 rockmoose.
using came casing help readabilty IMO. at least for longer table names and column names.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-01-07 : 10:10:06
I ought to write mine up ... this thread would make a useful resource for folk like Goalie35 to plunder bits from whoever's styles they like. We don't have to get anal about what is best!

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-01-07 : 10:14:18
quote:
Originally posted by spirit1

interesting how many different conventions are there, no?

i only disagree with number 4 rockmoose.
using came casing help readabilty IMO. at least for longer table names and column names.

Go with the flow & have fun! Else fight the flow


I reckon many would (prefer camelCasing over nocasing).
It's just a personal preference I have, I got weary of allTheCasingAllOverThePlace . Now I save some <shift> keystrokes.

I find it works better with procnames and functions, because there you tend to have a lot of functionality descriptions imbedded with the name:
spGetThis
spGetThat
fnLogThis
spCreateTrace

And the improved readability really helps.

For table and column names, well I find the names are "more atomic" and imc (imo) are very readable anyway.
And all lowercase is a very easy convention to follow.


>> interesting how many different conventions are there, no?
It would be interesting to set up an "official" sql team naming convention. Anyone?

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-01-07 : 10:20:47
quote:
I ought to write mine up ... this thread would make a useful resource for folk like Goalie35 to plunder bits from whoever's styles they like. We don't have to get anal about what is best!

I was thinking along the lines of a "base naming convention" that the majority can subscribe to as being "very good" in most cases.

>> We don't have to get anal about what is best!
I'll keep an eye on you then

what about nulls?

kidding...

rockmoose
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site