What is the most common naming convention for SQL Database tables? Was it historically one way and now it's better to do another way? What are the most common practices now?
+4
A:
I always use plural for table names and singular for column names. Not that there's any real technical reason for it, that's just what I prefer.
Doesn't much matter, so long as you are consistent.
I.e.
+========+ +==========+ | Posts | | Users | +--------+ +----------+ | idPost | |-> | idUser | | Poster | <-| | Name | +========+ +==========+
My reasoning for this is what happens when you write the actual query:
SELECT idPost, Name FROM Posts
INNER JOIN Users ON Poster = idUser
If you use singular, it looks like you're selecting from a post, rather than from the set of all posts, and joining to a single user, instead of all users.
Billy ONeal
2010-09-11 00:11:39
Agreed.........
Zerofiz
2010-09-11 00:14:09
It only "looks that way" if you are conditioned to see it that way. If you used singular for a while it wouldn't look so funny any more. That's the way of all things.
Emtucifor
2010-09-11 04:26:49
@Emtucifor: I believe that was the point of "Doesn't matter, so long as you are consistent." That's what makes most sense to me, so that's what I use. If you like singular better, use that. But don't mix n' match.
Billy ONeal
2010-09-11 22:26:55
Only reason I don't like this is you do something like "SELECT Name FROM User" -- which looks like you're selecting one name, not all of them. But +1 for another good answer.
Billy ONeal
2010-09-11 00:18:39
Nope, multiple rows can come back. But I'm glad you like it. Thanks, Billy.
duffymo
2010-09-11 00:24:31
@duffymo: Multiple instances of "User". The object in the table is a "User".
S.Lott
2010-09-11 00:33:31
Well I converted from plural to singular many years ago and I think it is more readable in general. It's a bit like putting 'tbl' on the front of a table. It's a redundancy. My vote +1
MikeAinOz
2010-09-11 00:50:41
I agree all the way. Columns represent plurals, too, and nobody argues those should be plural. It's all how you think about it. Personally, I like saving a letter on every table. It's cleaner.
Emtucifor
2010-09-11 04:25:36
@Emtucifor: "Columns represent plurals, too"? What? A User (singular) has a Name (singular). While there are multiple names in the database, each User instance has a Name. There are multiple databases in the universe. And possibly multiple universes. We don't say "Emtucifors" to be sure to include all possible instances in all possible universes. That seems silly. One User is the object. The table is filled User objects. Hence the name "User". We're not "saving a character". We're being explicit about the object class.
S.Lott
2010-09-12 18:35:20
@s.lott I agree that when using singular (my strong preference) the way that makes sense to think about it is "the User table" or "the User class." We're using our label differently. Instead of naming the objects that are in the table we are denoting the type of thing the table stores. A jar might be labeled *screws* instead of *screw* because we're naming its contents. But a toy with holes in the shape of a circle, triangle, and square could have singular labels next to the holes, even though many blocks of these shapes could be intended to pass in.
Emtucifor
2010-09-12 21:09:02
Now to address that columns represent plurals, they do. The Name column has many names in it, but we *think about it differently* than tables. We are row-oriented, imagining that the instance of each column (a particular Name) belongs to the row first, then the table second, therefore the table has many rows, each containing one Name, so the column should be singular. But this is arbitrary, a function of storage. The name column has many names in it and someone somewhere could less than absurdly argue it should be called the Names column... the **Names** of those **Users**!
Emtucifor
2010-09-12 21:10:54
My point in saying that columns represent plurals was to call out how we think about columns and say that we are making a similar transition in how we think about tables to name them singularly. No longer are we thinking in terms of each row being one User and the table a container for them (like "screws" on the jar) but that the table is an interface of a kind of object of type User.
Emtucifor
2010-09-12 21:12:47
Ultimately, databases, schemas, tables, names, and columns are coordinates in a many-dimensional space to locate pieces of information. We use syntax that addresses the various dimensions differently, but that is not a requirement of the space. We named columns, but why couldn't we name rows? `SELECT 1254, 2857, 6543 FROM Table WHERE Columns = '*'` could be just as legitimate as `SELECT * FROM Table WHERE RowID IN (1254, 2857, 6543)`. It's all applying selectors against dimensions.
Emtucifor
2010-09-12 21:16:17
"The name column has many names in it ". No it doesn't. It's an attribute of a row. It has ONE attribute value for the object. There's no "transition". It's singular. Unless you have a 1NF violation, and have a repeating group. If you don't claim a column is singular, you can't detect a 1NF violation. Column names are singular. Except when you have a 1NF violation, then thery're plural.
S.Lott
2010-09-12 21:17:26
A:
The standard pattern for LINQ to SQL (and EF,presumably), Ruby/Rails, etc. -- that is frameworks that choose convention over configuration -- is to use plural table names.
tvanfosson
2010-09-11 00:17:38
+3
A:
plural for table names - because tables store users, products, items, and so on. singular names for models as they are single item - User, Product, Item. for table fields I conform to mysql naming convention - user_id, product_price, item_count.
Use any of them, but use consistently - that would be my answer after all.
Tomasz Kowalczyk
2010-09-11 00:45:22
A:
I usually name the table depending on how I intuitively relate to it.
CheeseConQueso
2010-09-11 00:49:51
I'm sorry, but 110% disagree here. Nothing worse than working on a piece of code and having to stop your train of thought to look up how the table is named in the schema.
Billy ONeal
2010-09-11 01:02:22
yeah, good point.... maybe id start with that on the first table def, and then stick to that convention (s or pl)
CheeseConQueso
2010-10-06 01:47:15