asked
Tables Naming Standard
Over internet you can read a lot about tables naming standards. What I'm interested in is your opinion about using singular vs plural form of table names. E.g. Client vs Clients When looking over internet, you will find opinions 50 to 50 per cent when asking about singular vs plural form. I would like to hear opinion of people here on Ask.
I tend to go with singular, descriptive names. Singular or plural doesn't really bother me that much, but it seems that a table named Balcony makes more sense than one named Balconies. I don't get the point of the extra letters that don't really change the overall description of the entity being stored. That said, generally, no abbreviations unless they're very standard, such as ID for Identity. Camel case most of the time... Pretty much name my database objects by what they do for the business. No "hungarian" crap (in quotes because Hungarians & their culture are fine, "hungarian notation" is an abomination). Mostly though, let's just make it clear. I'm cool with Employees or Employee. I might be OK with Emp. But Emply... oh hell no! I had to deal with a naming standard, focused on Oracle of course, that resulted in Ddltbl for Deductible. Figure out why that might make me crazy. So real words or commonly accpeted abbreviations only please.
@Grant Fritchey Yea, Ddltbl is a bit strange. I would not like but at least would understand the logic behind Ddctbl (omitting all vowels which might be beneficial in Oracle where the object names are, or at least were in the versions I knew, limited to 30 characters). Omitting vowels could be simply cultural. For example, both Arabic and Hebrew omit the vowels when spelling as those could be deduced from content. They still pronounce them though.
@Grant Fritchey By now I am actually sworn by Pascal case and single form. At my first place of employment I had to use all lower case with underscores between the words, which also made sense. We had a coding standards document which had it plain and simple: any deviation from the all lower case with correctly spelled English words separated by underscores (no spaces, no keywords in brackets of course) is a ground for the termintation of employment. Sounded kinda brutal, but I loved it. This way we only had to use but one primitive routine to "translate" the column names into user friendly Pascal case descriptive headers to have consistency for all the grids out there. When I first time saw that BI does it out of the box, all you have to do is use either single form and Pascal Case or underscores between the words, I started using the former because it looks better with the same result. I still remember that document though and do use all lower case for whatever T-SQL I write :)
have you seen the tables in the PerfAnalysis db? All with tbl prefixes !!!
@fatherjack, horrifying @Oleg, yeah, it sort of worked, but the application was weak in the extreme. They wanted idntfctn for Identification. I fought for six months for ID.There was no flexibility.
I remember working at one place where there was a 100 page book of standard abbreviations - needless to say, they weren't particularly memorable.
With emphasis that this is just my opinion, I prefer using plurals for most tables. It simply seems to fit grammatically and aesthetically better for me, and I tend to mentally think that the singluar form makes more sense for one row of data. While I acknolwedge that is mostly an aesthetic opinion, I do think it is best that each database (or better, each organization) does have onee fixed standard. There is no real advantage to singular over plural, but mixing them together tends to make it harder to remember the table names and can lead to confusion (or at least greater need to look up names0.
My preference is to have something descriptive, **without** a prefix of tbl or t_ etc, and use the plural form as it seems to relate to an object that contains lots of records of the singular. As @timothyawiseman says, grammatically it makes discussion and disabmiguity easier. IE the Clients table holds Client records. All of the Clients are in the Clients table. Select a Client from the Clients table...
Plural form appears to be more convenient. This said, it appears that there is no common agreement even within the same shops. For example, the databases which are created by Microsoft for learning purposes (Pubs, Northwind, AdventureWorks) tend to use single form for tables and views names (Person.Address, HumanResources.Department ets) but at the same time many system tables in resource database as well as their respective system views in the user databases tend to use the plural form. Go figure. In any case, table and column names consisting of easy to understand Pascal case English words make it ridiculously really easy to create front ends with user friendly headers, like, for example in BI, where it happens automagically that the column **FirstName** becomes **First Name** in the grid header. The same can be achieved with no or little code by using underscores between the words for table and column names. Disclaimer: We use single form for table names (Country, CostCenter) where I work unless the plural form is unavoidable (such as in ProjectManHours).
Keep in mind that the folks who design the sample databases are not the same folks who design the system catalogs, so drawing any conclusions from similarities or differences between conventions in those two cases is futile. :-)
Yes, I've been frustrated by this myself. I think that the most important rule is consistency. If you always use plural, use it across the board everywhere. That way, you don't have to wonder if a table name has an "s" at the end or not. That's not easy to do, to me for instance the table for employees seems to sound better to be named "Employee," but a table for clients should be plural. Personally, I tend to make the table singular unless it sounds completly wrong. For example, I don't think a table for bills should be called "bill." 'Makes me think of Kill Bill. :-D edit: for bad English
Strange..... but I prefer singular table names. I think this stems from when I learned about data modelling and so I would model a 'Client' as an entity in an ER diagram. Then when moving from the logical model to the physical, the names would just stick. I understand the logical reasoning given by @TimothyAWiseman and @fatherjack, but its just preference!
Very good answers so far. My five cents: Logically, I think singularis is preferred, perhaps because I always do object oriented development and objects should be singularis, unless they are collections when pluralis would work fine. But in real life, I find myself often creating tables with pluralis names anyway, much to my own disliking. So I'm unfortunately not always consequent, and my libido seems to want pluralis. Prefixes and suffixes should of course be banned. However, if they are already in a database, they are. I have inherited two databases which are tightly connected - an E-commerce app and a catalogue app. Initially the catalogue tables were copied to the E-commerce db once per week. The database designers of these two apps thought it was a good idea to use _different_ prefixes for the two databases, to in the E-commerce DB know which is which. Therefore, in the same database we had: tbl\_user - users for the E-commerce app tblUser - users for the catalogue app Oh.. And of course, the tbl\_ prefix for tables is matched with sp\_ prefix for stored procedures... Etc. Bad, bad, bad naming conventions. But that's how it is, and we probably won't rewrite all the application code to get this sorted out. So even if I very much dislike the naming conventions in some databases, I think we'll stick to them for those specific databases, and when creating something new, there will be perfect names for all objects :)
If you want to change the naming scheme, you can do so gradually. Rename a table from tbl_whatever to whatever, then make a synonym tbl_whatever that points to whatever. This has some complications (e.g. you can't do everything to a synonym that you can to an object), but for a lot of OLTP apps this can work as a gradual migration instead of an offline operation.
There are many ways to gradually change both object names, bad coding in stored procedures etc., but bussiness won't ever prioritize that work before bussiness projects, so unless one wants to do it after hours, without being paid for it, what we have is what we get :)
As I understand it, Joe Celko is a little like Marmite (love it or hate it), but he wrote a book on this sort of thing: [SQL Programming Style][1] - He also wrote the book on a lot of things, including ISO and ASNI SQL standards. According to him, tables should be plural nouns (I agree) and should not be preceded with "tbl" or anything similar. He goes on to explain all sorts of other "style" principals. While I don't agree with them all, the book is certainly a good way of getting a cleaner guideline put together. I used this as a primer for setting up database programming guidelines at my current job. The main win is really having a standard at all, whether it fits to everyone else's view or not is pretty irrelevant. [1]:
http://www.amazon.com/Programming-Kaufmann-Management-Systems-ebook/dp/B0014EUCC2/ref=dp_kinw_strp_1?ie=UTF8&m=AG56TWVU5XWC2
Yeah, like or loathe something doesn't matter if it helps you make up your own mind.
Adding to [WilliamD](
http://ask.sqlservercentral.com/users/256/williamd/)'s answer, [Joe Celko's SQL Programming Style](
http://books.google.com/books?id=a9jtyioHfp8C&printsec=frontcover&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false) claims to be the only book on SQL heuristics, which may be reason enough for following his advice. Celko actually advocates collective or class table names (industry standard where applicable) rather than simply pluralized nouns. For example, he would prefer the collective name `Personnel` rather than the plural noun `Employees`. Similarly, `Payroll` rather than `Salaries`. (And presumably `Murder` rather than `Crows` ;)
Collective / class names seems a neat way of sidestepping the singular/plural argument. +1.
Not that I'm going to say anything that hasn't already been answered, but a few points: 1. Whether you choose singular or plural is up to you. You've already read about many of the reasons that some people pick one over the other; which of these reasons is/are important to you? Whatever they are, at least 100 times more important is that you document your standard, stick to it yourself, and enforce it amongst your team. 2. I prefer plural. Why? Because unless the table will always have exactly one row, it is a table of Employees, not a table of one Employee. Put another way: A table is a set. You have a set of Employees, not a set of Employee. 3. Collective names (e.g. EventLog vs. LogEntries, Calendar vs. Dates) are a great sidestep to this issue, but collective names are not always available (what is a collective alternative to Companies?) or do not always fit logically into an existing data model. Not just talking about table names but also how you refer to these entities in documentation and in spoken conversation. If your employees are referred to in all systems as EmployeeID, and you use Personnel to spare yourself the agony of typing an extra S, what is the key in that table? PersonnelID, or EmployeeID? (Please don't say ID.) Not if you choose PersonnelID to match the table name, you're going to have unintuitive joins like p.PersonnelID = eh.EmployeeID. Personally I prefer the key to have the same name throughout the model when possible, rather than be dependent on the table that it's in. 4. Not sure what you expect to gain here except to confirm that the split is going to be roughly 50/50 here, just like it is in all the other discussions you've reviewed to date (and probably all of the discussions you haven't reviewed, too).
