February 1, 2010 at 9:21 pm
Hi Every one, good morning.
currently my projects is in SQL 2005 and the project is moving to SQL 2008. when i am working on redesigning the data base i am having a doubt on naming the tables properly.
current version of data model the tables are segregated in 3 ways
1. tm_TableName: which holds all the master data
2. tr_TableName: which holds the regular transaction data
3. td_TableName: any other type of data will go in these tables
could you any one suggest me what will be the best way to handle this in re write. i am thinking to just go as it is. but want to follow the standards.
and having another doubt, what is better practice to handle the internal id's (integer) . right now using identity column with seeds and increment, like start with 1001 and increment 1. what should be the name of the column
Thank You
February 1, 2010 at 10:39 pm
raju.tanneeru (2/1/2010)
Hi Every one, good morning.currently my projects is in SQL 2005 and the project is moving to SQL 2008. when i am working on redesigning the data base i am having a doubt on naming the tables properly.
current version of data model the tables are segregated in 3 ways
1. tm_TableName: which holds all the master data
2. tr_TableName: which holds the regular transaction data
3. td_TableName: any other type of data will go in these tables
could you any one suggest me what will be the best way to handle this in re write. i am thinking to just go as it is. but want to follow the standards.
and having another doubt, what is better practice to handle the internal id's (integer) . right now using identity column with seeds and increment, like start with 1001 and increment 1. what should be the name of the column
Thank You
you must follow the guidelines of your application nomenclature document (if it exists). table naming conventions based on your requirement. For example
User table structure may be
User_ID
User_Name
User_CityID
where city table may be
City_ID
etc.
where the data is splitted in groups like in master and in transaction:
TranMEmp_ID
TranDEmp_TranMEmpID
etc.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
February 1, 2010 at 10:44 pm
raju i can only tell you my experiences and the way we do things at my shop; you can review them and see what you like, since there's no real rules to follow:
for prefixes, our database supports multiple applications that share a common database, so tables related to a specific application have a matching prefix; cmsBusinesses, cmsContacts, etc for a contact management system for example. in your example, we might have a table named appTransactions and appTranDetails for example, instead of a prefix to imply a master-detail relationship;
1. no underscores allowed in table names; CamelCase is preferred, and the table name must imply the content/business purpose of the table.
2. every table has an identity as the first column. the column name is always the table name + 'ID' or 'TBLKEY' or something so it's obvious it's the key. cityid for tbCity, stateid for tbState, etc. we leave identities starting at the default of 1,1 unless a business reason needs it otherwise.
3. foreign keys in child tables have the column name the same as the key it is pointing to; so an cmsAddress table has a column named cityID pointing to tbCity(cityID); in the cases where we need more than one, the column name must still be a part of the column name, adn just add a prefix: percityId and buscityID for collections of addresses for personal vs business; helps later so you can confirm that every column ending in "ID" is either a PK or FK, for example.
4. lookup/shared reference tables start with tb; tbCity,tbState,tbCounty; "TB" tables are shared lookups. if a table of say statuses is needed (Open,Closed,Pending) whatever, that table would be for a specific application, so cmsStatus and appStatus might start out with the same values, but from experience, we found that eventually one app needs to change a shared table.
appCity for example, might be a subset of all cities, as not city is really needed.
5. try to avoid abbreviations if possible; in your example would a new developer intuitively understand that tm_,tr_ and td_ relationship?
or would something like AppMaster,AppMasterTransactions, and AppMasterDetails be more descriptive and intuitive?
same thing in column names...silly things like lglstatus vs LegalStatus and other abbreviations; made sense to the person who coded it at the time, but if you step back or someone else looks at it, would they know?
6. no Hungarian notation for data types, only their purpose; a perfect example is "amount" columns; we might name a column InvoiceAmt, and it's decimal or money for the data type, but some business processes capture whole dollars;sAddress, iInvoiceAmt and dInvoiceAmt might imply the data type, but that might need to change in the future; there is lots of info here about Hungarian Notation and it's pitfalls:
http://www.sqlservercentral.com/search/?q=hungarian+notation
now that i said what we strive for, let me also say this: there are exceptions to every rule above; some tables don't get an identity, sometimes a table gets an underscore to match a different business process, etc. but they change because there was a reason, and not for an arbitrary reason.
I hope others chime in and offer some more examples and guidelines they use as well.
Lowell
February 1, 2010 at 11:12 pm
Lowell,
Thanks for your reply. it is a great input to me to work on redesign the Data Model. even i do like having the table names fully readable and understandable.
Thanks aloot for taking your time
February 2, 2010 at 5:33 am
raju you are very welcome; you should get some other great feedback as other posters get their coffee and hit this thread as well.
Lowell
February 2, 2010 at 5:44 am
I've gotten farther and farther away from enforcing naming standards. Too much time working with CRM, Sharepoint and ORM tools. Basically, I'd make the table names clear. Instead of tm_TableName for a "master data" table, how about MasterDataTableName? Make it absolutely clear, to anyone, even business people, what each object represents. A few commonly used abbreviations are fine, "Org" instead of "Organization" for example, but other than that, use clear English statements for the table and column names.
I absolutely agree that a foreign key column should, except where there are exceptions, always be named the same as the primary key it relates to. Having multiple names for columns inside of a database just leads to confusion.
In general, focus on clarity and simplicity. There are enough complications that will arise that trying to determine what exactly the trdixuv_Column represents just shouldn't be a part of it.
"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
February 2, 2010 at 7:14 am
I think it's what works for you is best. Using schemas to group objects is one way in sql 2008, but if you have too many schemas it may lead to confusion and errors as no-one can remember which schema an object exists within.
I prefer to name things as to what they are/do. I worked with a project manager who tried to use character abbreviations to show this - it all ended up a mess as we ended up with too many variations.
You should keep to a standard, this is important in case you stray into binary databases. Set your standard, document it and make eveyone work to it. As I work for many clients I can say that generally everyone has a different view, which I may or may not agree with. I'm not convinced the examples you have given are actually a good idea however !
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply