Practical Methods: Naming Conventions
-
One of the things I have come across is that *not* prefixing or suffixing tables AND views may save the day on databases with large tables.
You maybe wondering "why?". Well you could change a table into a partitioned view ( with no client side changes ) or change a view into a table for materialization purposes ( again with no client-side changes)
These scenarios are not that uncommon and is something you may want to keep in mind.
Cheers,
* Noel -
Care to elaborate a bit more on this one?
-
Once again to the singular vs. plural topic: Of course there are valid arguments for using plural.
But most of them are weak especially the one that says: "An employee table contains employees so let us name it employees!". A table can contain less than 2 rows, do you want to rename it?
You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name
And please don't counter like: "Customers is genitive without the apostrophe."
-
Just echoing other comments opposed to hard-coding an objects type in the object's name ("t", "tbl", "v", "viw", "int" - prefix or suffix): it is a blueprint for disaster. As databases and applications grow and wane, tuning and other application factors change tables to views (partitioned views, tables moved to external databases and referenced through views, etc.), views become tables, and columns change datatypes (int -> bigint, char -> nvarchar). Many database folks working with huge fluid systems shudder at Hungarian notation and all that follows. It's only a matter of time before they have 50+ views that all begin with 'tbl'.
-Eddie
Eddie Wuerch
MCM: SQL -
"You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name "
Spoken like a true COBOL warrior.
Who needs set processing?
-
Aye, I agree with Eddie, can't stand these daft prefixes people get taught at university/polly.
Plural/Singular it doesn't really matter that much, as long as it scans well. Closer you get to natural english the better.
Steve's point about object prefix is absolutely spot on. When you get developers who insist on insert_something, get_something, add_something else its a pain in the jacksy.
They say 'oh but we know its insertx' but is it? it might be 'addx' or 'newx' or gok whatever someone was thinking at the time.
When you need to review the code you mind numbingly scroll up and down 100's of get_x procs. The whole verb_Object construct is just not fun. Object_verb is so much more usable.
Another thing that gets me is when developers assume that the column is always a part of the table, and you always reference it as such.
eg
create table SendMoney(from, to, reason, time)
create table DispatchRider(from, to, reason, time)
So now from can mean several things. of course you wouldn't use From anyway, would you. What with it being a reserved word and all.
-
I vote thumbs down on prefixes. I am now dealing with 400+ tables that all begin with TBL_XX_tablename, where XX is the initials of the guy who created it. The idea is if you have any questions about the table, you can wander over to the Creator's cube and ask him (we have a rich oral tradition here). Besides, if you can't tell its a table from reading the code, perhaps you should be in IT management...
I do practice the object-verb naming convention. If you have a stored proc that adds a customer, call it CustomerAdd. If you do it the otherway around, you have a bunch of procs that all line up in the treeview like
AddCustomer
AddThat
AddThis
when what you want is
CustomerAdd
CustomerDelete
CustomerUpdate.
There is no "i" in team, but idiot has two. -
anyone from the objectVerb, no prefix school looking for a job in london?
-
Yup, but I have very strict requests. Never hurt to ask however.
-
London, sign me up if you'd like a DBA with 3 decades of experience who loves to sip a pint and watch football at the corner pub.
-
I agree with a lot stated here. Couple of comments from my general experience:
Not sure I necessarily agree with prefixing objects
with t for table, v for view. From experience, this can lead to eye fatigue.
I like to add underscore so the intended table functionality can quickly be scanned.
t_
Though you might say tCustomer and tVendors is easily distinguishable, compare that to t_Customers, t_Vendors. I at least feel the prefix still allows quick discernment of a table object, but I found the underscore promotes less
eye fatigue with objects.
Regarding indexes:
I usually prefix cidx for clustered, idx for non-clustered in front of the index name. That way I can quickly scan an index list and see what I'm clustering on.
Regarding stored proceudres/views:
vw_
asp_
Again the underscore allows better readibility of the view/proc name.
I prefer asp as a prefix namely because procs list ahead of the system
sp_. I guess p would work as well; however, the difference between
p and sp, though trivial, is very close in sorting and placement in
alphabet. I too at one time used variations of prefixes for procedures
and discovered 3 letter prefix _ worked best and the a catches the eye
whereas the e can get lazy with a p sp combination. I settled on
asp_ as a preference because 'a' just jumps out ahead of 'p','s'.
Again these are just some personal preferences from working with large DBs
of mine.
We also generate a lot of temp and lookup tables in our environment.
We us lu for lookup with these tables.
In regards to : Use mixed case rather than underscores (in most cases) to indicate word breaks . Use "pCustomerAddressCreate" instead of "pcustomer_address_create".
Not sure I agree with that either for same reasons as listed above, but to each his
own I guess.
Of course avoid usage of company names when you working as a contractor across
several companies doing work.
Sales
SalesHistory
SalesCategories
SalesReps
SalesContacts
...ad infinitum....
WHile this may help you as a hired contractor keep your clients code straight,
this methodology drives internal contractors crazy.
-
Hi EM,
One of the issues noted with prefixes is that the type of object can actually be changed. For instance, you might have a table named "tSales" or even "t_Sales" to use your recommendation. What happens when you change "t_Sales" to a View? You now have to change the name to "vw_Sales" on the server, and you have to change all client code that used to query the "t_Sales" table.
If the justification for using prefixes like "t_" and "vw_", etc., is to make it easier on the eyes in Enterprise Manager, this should not be a problem in SQL 2005. You can prefix all your objects with a schema (other than "dbo.") in SQL 2005, so all your "Sales"-related tables (for instance) will group together nicely (and separately from "dbo.") in SSMS without the need for redundant prefixes in your naming convention.
To use your example, you could group all the Sales-related tables into a "Sales" schema on SQL 2005:
Sales.Sales
Sales.History
Sales.Categories
Sales.Reps
Sales.Contacts
Same goes for SP's and UDF's. You can use the simple "ObjectAction" naming convention and assign them to the proper respective schemas.
-
No underscores for me, either. Fat fingers.
"If it was hard to write, it should be hard to read, and even harder to maintain."
There is no "i" in team, but idiot has two. -
table , table [transaction]
or
table tuser, table ttransaction
the t prefix helps when it comes to some common reserved words.
I don't mind using t and v as prefixes on tables and views, when it comes to partitioned views the t prefix can be used,
since logically the partitioned view has the physical role of a table.
I also find it good to be able to easily distinguish between the 2 different objects (t+v) in code.
2 cents on why I like t + v prefixes on t+v database objects.
rockmoose
You must unlearn what You have learnt -
A number of people have mentioned singular & plural when naming a table, as Mr.Celko said - it's a set, hence plural makes sense.
To be slightly obtuse here, use of t or v or pk & fk in naming is useful to someone such as myself who has moved between many systems. These simple conventions shorten the amount of time it takes to get aquainted with a new system. Purity of design versus practicality is something you should ask the guy paying you about, he will always opt for the cheapest pragmatic solution. So why don't you all try that approach.
proy
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply