标签云

微信群

扫码加入我们

WeChat QR Code

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:Should table names be plural?Should column names be singular?Should I prefix tables or columns?Should I use any case in naming items?Are there any recommended guidelines out there for naming items in a database?


I think we should name plural for Tables and singular for columns.

2019年06月25日12分16秒

I see a table as "storage" with multiple items, not single "entity" so I name it plural. When I mapped tables into objects, I would name the objects singular. This is just my personal opinion.

2019年06月25日12分16秒

Tryinko Using ID all over the place is LIVING HELL for anyone doing joins of multiple tables. There's no possible way that the slight advantage of knowing this is the PK outweighs the incredible annoyance of re-aliasing the dang ID column in every bloody query over and over again. If you want a way to denote PK in a table, make it the first column. Also, denoting FKs in the names of columns is in my mind another solidly evil anti-pattern.

2019年06月25日12分16秒

Have a look at this Answer.

2019年06月25日12分16秒

wilsonmar.com/sql_adventureworks.htm is an excellent analysis of the AdventureWorks schema.

2019年06月26日12分16秒

I wouldn't rely on Microsoft for any standard - if you look at their northwind database you'll see they use Plural Tables, Singular Column Names, Schema Prefixes for Tables, Table Prefixes for Primary Key Columns, Hungarian-esque Constraint Prefixes and worst of all SPACES " " for multi-word table names.Additionally system tables for SQLServer use plurals so it seems AdventureWorks was the black sheep in this bunch.

2019年06月26日12分16秒

I think the main issue here is that the Singular table name crowd seem to consider the table as the entity, rather than the row in the table which the Plural crowd does. You have to ask your self which it is. If the table is just a container of rows, isn't it more logical to use plural naming? You would never name a collection in code singular, then why would you name the table singular? Why the inconsistency? I hear all the arguments about how they sort and use in joins but those all seem very flimsy arguments. If it all comes down to preference, I will go with the consistency and pluralize.

2019年06月25日12分16秒

Also consider which direction the tide is going. It seems like its going in the direction of plural table names especially since all the system tables in SQL Server are all plural, and the default for Entity Framework is plural out of the box. If that's Microsoft's stance, I want to go the direction where we will be in 20 years. Even Oracle's database conventions say plural table names. Just think how many c# developers hated the "var" keyword when it was introduced, now its the widely accepted way to define variables.

2019年06月26日12分16秒

Jasmine - I see your point of view, though I think you inadvertently named your example table backwards."TableOfInvoices" should be shortened to "Invoices," which is what I prefer.You probably instead meant "InvoiceTable," which makes sense to shorten "Invoice."

2019年06月25日12分16秒

"Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another." Yes yes a million times yes. Can you tell our database was not designed that way? A personid might be refered to in any of a dozen different ways, very annoying to maintain. I've always kept to this rule in any database I had control over designing and it makes life much simpler.

2019年06月26日12分16秒

I think the primary key should just be "ID".Such a simple convention makes the primary key predictable and quickly identifiable.I would, however, prepend the table name ("PersonID") when its used as a foreign key in other tables.This convention could help distinguish between a primary key and foreign keys in the same table.

2019年06月25日12分16秒

Tryinko Using ID all over the place is LIVING HELL for anyone doing joins of multiple tables. There's no possible way that the slight advantage of knowing this is the PK outweighs the incredible annoyance of re-aliasing the dang ID column in every bloody query over and over again. If you want a way to denote PK in a table, make it the first column. Also, denoting FKs in the names of columns is in my mind another solidly evil anti-pattern.

2019年06月25日12分16秒

Triynko if you use just "ID", it also become programatically impossible to determine the table it belongs to.With the table name prefix, you can simply cut off the last two digits of a primary key and know the table name it belongs to via code.A lot of times IT and DBA people don't realize that there are coding advantages for programmers in designing databases in certain ways.

2019年06月26日12分16秒

ErikE I mean you don't know whether CustomerID is the primary key from the Customer table, or a foreign key in some other table. It's a minor issue. Why would you want to use poor names like c? CustomerID = Customer.ID is very clear in that you see that you are joining a foreign key with a primary key; it is not redundant as the two sides are two different things. Single character naming is poor practice IMO.

2019年06月25日12分16秒

Emtucifor: In English, we don't say "Look at all the person out there in that crowd of person!"Having a conceptual problem with things that are multiple being referred to by a singular word is to be expected.It's neither usual nor proper."Data" is exceptional and often used to refer to a piece of a volume of substance, much like "cake"."Would you like (a piece of) cake?"Naming a table "People" because it contains information on multiple individuals makes far more sense than naming it "Person".A data class named "Person" for the ROW makes sense, as do singular column names.

2019年06月26日12分16秒

Emtucifor:Ultimately all language is arbitrary and conventional.I was just arguing that conventionally we refer to a collection of items as the plural of the type of item therein.So a collection of rows where each row has information about a single person would be refferred to as a collection of People.But if you want to refer to it as a collection of Person, go right ahead.

2019年06月26日12分16秒

Emtucifor: Yes, lol.Naming the table "PersonCollection" would be equivalent to naming it "People".Contrast that with naming such a collection just "Person", which does not make sense :)

2019年06月26日12分16秒

Emtucifor: Then let's think of it from another angle to put the naming convention in a context.Suppose you have object classes for representing both the row and the table."Person" obviously makes sense for the class that represents a row of data.If you're table was also named "Person", then you might have a naming conflict or some confusion.I just think that it makes more sense to name objects with accurate plurality.A row with data about a person should be called Person, and a table with information about people or multiple persons is called People, PersonCollection, Persons, etc.

2019年06月26日12分16秒

Josh M. Well, not either way you go. If you go with my way you can alias the People table as "person" and have SELECT person.Name. Problem solved. ;-)

2019年06月25日12分16秒

Especially for number 3, we had a agroup of folks who all got hired from the same company and they tried to impose their old naming standard (which none of the rest of us used) on anything they did. Very annoying.

2019年06月25日12分16秒

Certainly makes the SQL unreadable; but i think i can translate.cust_nm should be CustomerName, booking_dt should be BookingDate. reg_customer, well i have no idea what that is.

2019年06月26日12分16秒

Ian.The intention is that you stick to the naming convension your used to, and keep it consistent.I ALWAYS know that any date field is _dt, any name field is _nm. 'reg' is an example, of a "registration" system (bookings, customers etc) and all the related tables would have the same prefix.But each to their own...

2019年06月25日12分16秒

i agree that a particular standard is not as important as having a consistent standard. But some standards are wrong. DB2 and column names like CSPTCN, CSPTLN, CSPTMN, CSDLN. People should learn that long names have been invented - we can afford to make things readable.

2019年06月25日12分16秒

Throughout the years, I have added new columns at the end of my tables in the app I developed and market. Sometimes, I use english names in my columns, sometimes I use spanish and sometimes I re-use columns for something else, instead of deleting them and adding a new column with a proper descriptive name for what it is used. I purposely did this in order to OBFUSCATE my source code in case someone else tries to hack or reverse-engineer my code. Only I can understand it, someone else will get frustrated!..This way, they always have to rely on me for anything!

2019年06月26日12分16秒

Adding some clarity to number 3 - prefixes are a way of embedding metadata into the column name.There should be no need to do this in any modern DB for the same reasons as (overuse of) Hungarian notation.

2019年06月25日12分16秒

`select top 15 from order' or 'select top 15 from orders'?The latter is my (human) preference.

2019年06月25日12分16秒

Ian Boyd: Yep: SELECT TOP 100 * FROM Report R INNER JOIN VisitReport VR ON R.ReportID = VR.ReportID. It all depends on how you think about it. If you put a picture of a lemon on a canister, you'd know there were lemons inside, without needing two lemons on the outside to indicate that it could be plural. Sure, you might label it with the written word "lemons." But it might just as well be "lemon". To acquire the resource named "lemon", go here.

2019年06月25日12分16秒

add $0.01 for using UpperCase in column names and add another $0.01 for using underscore in column names so that its easier to distinguish column names in plain sight. Total = My $0.02 donation to you!

2019年06月25日12分16秒

"A table should be named after the entity it represents"A table is a collection of entities.While a table is also an entity, it is an entity of type "Table" which is pointless to add to its name.

2019年06月26日12分16秒

-1: The referenced text has nothing to do with ISO/IEC 11179. The referenced wikipedia page should not be trusted; read the actual standard instead (metadata-standards.org/11179/#A5)

2019年06月25日12分16秒

onedaywhen: I don't know enough about the subject to correct the wikipedia page; Also, the wikipedia page is not so much wrong as it is misleading - it doesn't explicitly say that ISO/IEC 11179 includes the database naming conventions, it just says that "ISO/IEC 11179 is applicable when naming tables and columns within a relational database". It then goes on to provide an example of naming conventions that might be used for relational database. It lets you think that the example is something taken from the standard, when it's really something made up by the writer of the wikipedia article.

2019年06月25日12分16秒

SELECT * FROM people AS person WHERE person.name = 'Greg' sounds the most natural to me.

2019年06月25日12分16秒

Zuko Mostly, the naming convention for table primary key is <table name><id>, for example PersonID or Person_ID etc. Therefore, it makes more sense that you NOT name your tables in plural as each record is a separate person not people.

2019年06月26日12分16秒

Your answer would be more accessible (=better) if you gave a summary here. Great pointer, though!

2019年06月26日12分16秒

Then you can no longer reliably find every line of code that touches a particular column... Isn't that the point?

2019年06月26日12分16秒

Raveren - You still can. If all you do is "SELECT *", then the query is irrelevant for this purpose. When/If later, you use the results of that query, you have to use the column name to do something with its data, so that is the place you need to worry about in your code, not the SQL statement.

2019年06月25日12分16秒

I wouls be curious as to what situations require SELECT *? I certainly would not want anyone to use that in production code. Yes it is useful for ad hoc queries and for finding out which piece of data is making your multiple join query results be odd, but I can think of no place in production code where it is required.

2019年06月26日12分16秒

Unless you are coding your whole app in a non-OO language, then having a decent ORM layer makes this argument redundant.

2019年06月25日12分16秒

So due to this answer, I decided to try using table prefixes on a large project and thought I'd report back. It did make refactoring tables extremely easy, which was awesome! However, it was a bigger pain than I anticipated. Our database had lots of complex named tables. It's easy to remember Cust is the prefix for Customer, but not as easy to remember the prefix for HazardVerificationMethod. Every time I wrote a table or field I had to pause to think about the prefix. In the end I decided speed and convenience was more important than searchability, but I did feel it was a valuable experience.

2019年06月25日12分16秒

What the heck is CapitalCase?

2019年06月26日12分16秒

ViRuSTriNiTy he probably meant pascal case

2019年06月26日12分16秒

Keith, on number #3 I do both, and I'm inconsistent (but I digress), but I do not get why it is bad to have a descriptive column name as long as it is not overboard, same with a table, a variable, etc.

2019年06月26日12分16秒

johnny it's not bad, as such, just not needed. Why type stuff you don't have to? Also most intellisense mainly uses the start of the name, so if you have Product.ProductName, Product.ProductID, Product.ProductPrice etc typing Product.P gives you all the prefixed fields.

2019年06月26日12分16秒

OMG. NO. Table names DEFINITELY plural.It's a COLLECTION.It has multiple things in it. "select * from PEOPLE". You're not selecting from a single person, you're selecting from multiple PEOPLE!

2019年06月25日12分16秒

I've always liked the way that the select statement sounds better if it is plural. SELECT id,name FROM contacts WHERE email_address LIKE '%gmail%' tables plural, columns singular.Again always a matter of personal opinion.

1970年01月01日00分03秒

tables are by definition relations. which are in fact singular.prefixes suck.Have you ever needed to change a table into a view or vice-versa? try that with prefixes.what difference does it make if it is a view or a table?

2019年06月25日12分16秒

A herd is a group of sheep. A User is not a group of users.

2019年06月25日12分16秒

Note the standards used: tables hold multiple things, users have one first name, T-SQL keywords in uppercase, table definitions in Pascal case.

2019年06月25日12分16秒

typo: Lastname should be LastName

2019年06月26日12分16秒

So you are saying the table is the entity? Or is the row in the table the entity? To me a table is a collection of rows - hence a collection of entities which implies plural.

2019年06月26日12分16秒

As you mentioned, you cannot rely on every case having table.column.Programmers will forget in one place and then your global find and replace just broke your whole program.Or you'll make it a rule and someone will think he's fulfilling the rule by using an alias of the table, thus again foiling a global find.In addition, if you want to organize your code by having some sort of database class (which any good programmer will), there will be times when you'll just pass a column name to a db function or just have the column name alone in a variable.

2019年06月26日12分16秒

janb: I totally support your answer. I want also to add that using text search to find dependencies is barbarian way to navigate code. Once people get rid of that barbarian search practice - they will start using good naming, which is table.column. So the problem is not naming style, the problem is bad tools made for barbarians.

2019年06月25日12分16秒

Your argument is flawed. The problem with it is it works both ways and doesn't add any advantage. You say, to solve this, just always write table.column, since you are already writing table_column. Well, you can also say just write table_column because you are already writing table.column. In other words, there is no difference between your answer other than it introduces possible errors and doesn't enforce conventions. It's the reason we have a 'private' keyword'. We could trust programmers to always use class variables correctly, but the keyword enforces it and eliminates possible errors.

2019年06月26日12分16秒

although what Oracle suggest it totally opposite to link linke above. find what Oracle says here..ss64.com/ora/syntax-naming.html

2019年06月25日12分16秒

This answer has already been given almost 3 years ago...

2019年06月26日12分16秒

Oracle's naming convention was the funniest of them all.. e.g.PATIENTS would have a primary key called pa_patient_id_pk !!

2019年06月25日12分16秒

ChristianName ... that's an odd convention.

2019年06月25日12分16秒

Serial numbers on your tables? Does anyone seriously think this makes sense works for the developers?

2019年06月25日12分16秒

Since this example brought it up... I'm personally against uppercasing acronyms in table or column names, as I think it makes it trickier to read. So in this case, I would say StudentId is preferable to StudentID. Not a big deal when the acronym is at the end, but I've seen countless examples in my job where acronyms were in the Front or middle of the name, and it made it more difficult to parse in your mind. Ex: StudentABCSSN vs StudentAbcSsn.

2019年06月25日12分16秒