What is the best practice in naming your "user" table?

Question

Here's three best practices I try to follow when naming tables:

Never name a table with plural (such as "users")
Never name a table using a reserved keyword (such as "user")
Never prefix your table name with "tbl" or some other object type prefix

Keeping all this in mind, how do you recommend naming the table that will hold user identities?

Consistency might be just as important as the actual policy. — miku, Mar 13 '11 at 16:20
I am using a table named USER in mysql with ORM. Why would you say it is a reserved keyword? — Adil Mehmood, Mar 13 '11 at 16:21
Adil Mehmood, "user" is not a reserved word in MySQL but it is in other RDMS's such as MS SQL Server. — HK1, Mar 13 '11 at 16:28
It's also reserved in PostgreSQL, but can be used if quoted: select * from "user"; — Alex Howansky, Mar 13 '11 at 17:42
I might add that I think it is also a best practice to use table names and field names that could be migrated to a different RDBMS without fear of encountering errors from having used reserved words. — HK1, Mar 14 '11 at 12:54
On the other hand, prefixing your tables with a system or application code allows multiple systems to share a schema or database. — Gilbert Le Blanc, Mar 15 '11 at 13:23
@Gilbert, I do think prefixing a table with a "domain name" (application name or code, not a web domain name) is not a bad idea and is different than the object prefixing I was referring to. — HK1, Mar 16 '11 at 00:59

score 40 · Accepted Answer · edited May 23 '17 at 12:17

40

I agree, do not use any reserved words, or quoted or bracketed or escaped forms of reserved words.

Name the User table Person.

You may be interested in this answer and google for the ISO standard 11179 for naming Guidelines

edited May 23 '17 at 12:17

Community

1
1

answered Mar 14 '11 at 09:46

PerformanceDBA

32,198
10
64
90

Are you sure about that ISO nr? I couldn't find anything when I googled. Does it have a name I can google instead? – Ronnis Mar 14 '11 at 11:42
I think maybe he was referring to ISO 11179. I'm not saying that ISO document is irrelevant to my question but I do think it is on a different/deeper level than the type of answer I was expecting. "Best practices" do not always coincide directly with the ideals presented in these types of documents but I admit I'm saying that without having read through ISO 11179. – HK1 Mar 14 '11 at 12:50
PerformanceDBA, thanks for the link to the other SO question about relational table naming conventions. I did read through that a few weeks back and have been applying much of what I read. However, that particular post doesn't address the particular problem I've ran into here where the best-fitting table name is a reserved word. – HK1 Mar 14 '11 at 12:52
1

@HK1. I have reformatted my answer, I did provide an alternative to "user". – PerformanceDBA Mar 15 '11 at 00:57
2

**Customer** is another one – Michel Feinstein Apr 23 '19 at 03:19

score 8 · Answer 2 · answered Mar 13 '11 at 16:45

I typically use something like member or account, depending on the application. That said, if you're using modern design tools and principles (e.g., a db abstraction layer or ORM with an object-oriented code base that separates business logic from data access), then table naming becomes fairly irrelevant. Your developers should only ever be accessing the database through a well-defined interface and not by hand-writing SQL that requires them to know the table name. For example, you could name the table account but map access to it via an object named User. Your developers shouldn't be thinking in terms of tables, but in terms of access objects, which aren't going to have the same restrictions on naming:

$user = new User($username);
$user->authenticate($password);

score 3 · Answer 3 · answered Mar 13 '11 at 16:21

3

Use a synonym. What word to use depends on what exactly you store in the table, but account strikes me as a good alternative. If you want to use a variation user I'd break the first guideline you mention, not the second or third: users is common enough that the inconsistency is essentially mnemonic.

answered Mar 13 '11 at 16:21

SirPavlova

133
3

account is already a table in HSQL, so if you use embedded HSQL for unit tests, you'll end up with a conflict – Andreas Dec 24 '16 at 19:21
How about Member? – Bu Saeed Apr 12 '21 at 14:28

score 2 · Answer 4 · answered Sep 26 '19 at 18:07

Do not use reserved words or quoted or escaped words for database table names.

If you really want to do this then you need to escape the names:

quotation marks: "user"
Java + JPA escaping: @Table(name = "\"user\"")

I highly not recommend using plural worlds for database table names like USERS. This is a BAD PRACTICE, against the SQL naming convention. Database table names need to be singular nouns.

I recommend using ACTOR for a database table name to store users details. This name is clear, understandable and enough general. It can be used for companies and individuals as well (not like ex. PERSON, which fit for only person but not companies).

score 1 · Answer 5 · answered Mar 15 '11 at 01:01

1

I use CakePHP rules even when I don't use the framework :

Table names are by convention lowercase and pluralized with multi-word table names separated by underscores. For example, a Model name of Ingredient expects the table name ingredients. Model name of EventRegistration would expect a table name of event_registrations.

answered Mar 15 '11 at 01:01

GG.

21,083
14
84
130

4

This is an interesting approach even though I've gathered the experts here at SO probably wouldn't agree with the plurals. – HK1 Mar 16 '11 at 01:02
1

@HK1 - the Django ORM does the same, uses plurals for table names, which I don't like. So I usually override in the table name in the `Meta` information for the class. – Tony Oct 30 '19 at 23:07

What is the best practice in naming your "user" table?

5 Answers5

Linked