table naming conventions
-
Does it make any sense to name tables such that similar tables sort together? I'm thinking of code lookup tables, like starting each with "c..." or something clever so that they are all together rather than scattered throughout the database. Has anyone written about this? THanks,
Sam
-
Naming conventions is a subject there exists many thoughts on. I think the most true 'truth' about it, is that it's incredibly hard to come up with a 'good' naming convention. It requires a lot of thought, more thought, considering pros and cons, more thoughts again, and in the end.. after a while, it's likely that something else comes around that makes the last desicion 'less good', so you want to change it..
Sometimes it pays to be 'clever', sometimes you risk getting bit by your own cleverness. I guess the answer is 'it depends'.
I sometimes too 'cluster related stuff' together by some naming convention that suits the particular occasion, but there's no general rule or reason to it. It highly depends on the underlying motifs of doing it in the first place. Are the gains better than the penalties? (nothing is ever for free you know)
As for the example, I'm not so sure I'd want to group codetables together by their only merit being 'codetables'. It feels redundant. I'm more in line of, if I really should try to 'relate by name', do it on another higher level, where I can find more distinct borders, like purpose or function or similar.
In the end, computers don't care much about how we name stuff, so it's important to realize that we do it only for ourselves, mostly for sorting purposes, but other times it can also make coding easier. It's similar to defining a protocol - the best results come when we are clever with a good reason. If we can't find a good enough reason, we probably shouldn't do it.
/Kenneth
-
Yes, I find that it is very good practice to name tables that have similiar functions together so that they are sorted together. It's a great visual tool when you want to see what types of tables you have and how many. It also helps when migrating data since you know by the name of the table what the function of the table is and thus you have a better idea how/when it should be migrated. Plus, its good for housekeeping. If you have apps that run on your db and some of the functionalilities in your apps are being retired, it makes it easier to determine which tables are affected (and may also be retired).
Have a good day,Norene Malaney
-
In the long run, you are better off just using common sense. Table names like Customers CustomerShiptoAddresses, Invoices, InvoiceDetails, Orders, OrdersDetails will naturally group (more-or-less) together and for those that don't, it will be easier to remember what the are when you look at it a year later. Much easier than if they start with "c" or "c_" or "eMytable" or "e_Mytable". Whoever happens to have to maintain your database will thank you later for rational, more traditional naming practices.
Here's another tip: Never use special characters in a table name (*&^%$#@!) even if SQL Server lets you do this. Yes, I have seen table name like %sold insead of pct_sold or percent_sold. I have seen table names like Phone# instead of PhoneNumber. Never use spaces in a table name or you will ever after have to put it in brackets in all queries you make like this: [Customer Address]. Never start a table name with a number. Only use letters, underscores or numbers in a table name (prefereably not numbers). Microsoft officially recomments tables be named using Pascal notation. That is all words together, capitalized to separate them, as in: InvoiceDetails, GeneralLedger, OwnerAddresses, PhoneNumbers, and so on.
Incidentally, all of the above recommendations go double for column names in your tables.
Good luck.
G. Milner
-
This really helps. Thanks. My takehome lesson is use grouped names only after careful thought and consideration of multiple factors. I decided not to use a prefix for now.
Sam
-
Being a web programmer and not a DBA, I guess, I have a little different slant on things.
It isn;t so much a matter of which naming convention you use.
Whether it be Table / column / variable - whatever.... the real secret to a successful naming convention is :-
make it simple to use / understand (prefix all variables with "var_" or use "caseSensitiveVariableNames" etc)
Document it - so it can be referred to / shared with your workgroup
- but the most important of all. regardless of what naming standards/methodology you end up with is:
USE YOUR NAMING CONVENTIONS ALL THE TIME.
Gavin Baumanis
Smith and Wesson. The original point and click device.
-
I would also recommend that you use a standard across the database.
The two most common ways are:
- All lowercase with underscores between the words percent_sold
- Uppercase for the first letter of each word PercentSold
Whichever one you use, use it for all objects in the database. I mean tables, columns, views, stored procedures, functions, etc. Nothing is harder to work with than when columns are one way and tables are another.
I also recommend against the prefix of tables. The customer tables when group naturally, as will the invoice tables. Just call tables what the hold and model reality.
One final note, pick either the singular or the plural and stick with it. I always use singular. Each row is a customer, so the table is called customer. Each row is an order, so the table is always singular. Microsoft tends to use the plural more. Just be consistent.
-
Hello!
I don't like prefix on tables. But now I'm creating a new database and I'm defining the "naming convention". Developers have asked me that they would like to know which tables have master data and which have transactional data.
Now they're quite used to use the prefix 'Ma' for master data tables. Is any way to give this information without using them? Maybe using Extended Properties?
How do you do it?
Josep
-
I like the idea of grouping tables/scripts/functions etc. into logical groups with some sort of naming convention.
My only comment is - keep it simple/obvious/intuitive.
Several years ago I inherited a database where the developer abbreviated most words (just to save on typing) using, for example, CstmrOrdrs for a Customer Orders table-name, and did the same for column-names, scripts, etc.) There were so many abbreviations (and inconsistencies) that he also created a small database to store the abbreviation-names and what they meant. After a while, I began to remember the more common tables/columns, but even now (several years later) I often have to check first before I can write a query or run a script - it wastes a lot of time.
Chris
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply