-1

Is there a guide or best case practise to help people on choosing the best database column names. I am not looking at syntactical naming conventions such as snake case, camel case or even singular or plural.

Here is an example from what we faced. We have a column that would indicate that a user would want to become an admin when signing up and based on this triggers would be initiated. Some of the names that we came up with were wants_to_be_admin, intends_to_be_admin, wants_to_administer, potential_admin. These column names seem equivalent, but we would like to standardise the way we come up with these names.

How would you propose to start making this guide/convention in a company?

closed as primarily opinion-based by McNets, Mr.Brownstone, mustaccio, Tom V, Marco Dec 17 '18 at 8:04

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

2

You need to think a bit more systemically. Earlier in my career I worked for a company that put together a naming standards committee. It's not so much what you name a specific data element as having rules for how to consistently construct names. Decide on standard abbreviations - will you use 'number' or 'nbr' or 'num'? 'amount', 'amnt', 'amt'? 'date', 'dte'? 'employee', 'emp'? Along that line, maybe some standard acronyms - 'dob' for 'date of birth', etc. Then think about grammar. Most data element names will be either just a noun ('employee') or an adjective + noun: invoice_date is and adjective ('invoice') + noun (date). In English, adjectives precede the noun, so it makes sense to name data elements that way, but it may be different in other languages. For nouns (tables names AND column names) be consistent about plural vs. singular. If you dig in more you will find arguments both ways but in the end it is more important just to be consistent. Some people chafe at standards, but my 30+ years experience tells me that in the end a rigorous standards makes life easier for everyone, especially those who have to maintain code. You should have a standards manual that everyone follows, and that specifies how to construct a name for everything - columns, tables, tablespaces, databases, files, servers, etc. (And BTW, your given example suggests to me a very poor data model, regardless of what you end up naming the column).

  • thanks for your input. I'm very curious why you find this data model poor. I've given it some thought but I don't see why it is so. I'm very open to design standards and it'll be great if you help me out here. Thanks. – tambakoo Dec 28 '18 at 8:05
  • On design - the whole 'wants to be admin' indicator strikes me as very odd. Just call it a gut feel for now. Triggers are also a red flag. I'd have to know more about the whole design, especially what the triggers are doing. Triggers have their usage, but the are quite often abused. google 'the trouble with triggers'. – EdStevens Dec 28 '18 at 12:42
1

(Ed has good points; here's my 2-cents.)

Do not prefix column names with the table name; it only clutters queries. When you do a JOIN, you do need to say which table they came from; usually, a 1- or 2- letter alias is sufficient and more precise than a prefix. Ditto for not prefixing tables with the database name. You can, where necessary, qualify a table name thus: dbname.tablename. (There is no .dbo. in MySQL.)

Many, but not necessarily all, tables will have id INT AUTO_INCREMENT. Decide on how to name them:

Plan A: Each table has id; use table (or alias) qualification to distinguish when JOINing.

Plan B: The foo table has foo_id, and any table referencing it also calls it foo_id. (Yeah, this contradicts my first point, but it is limited to ids.)

Don't bother with long index names. Essentially the only purpose for such is if you ever need to DROP an index.

Try to avoid confusable names: Employee vs Employer -- it can be challenging to read until the last letter to see which is which.

I agree with Ed about abbreviations. If your database talks about cars and trucks, don't bother with the verbose vehicle_identification_number when vin is probably already understood; if not the reader can quickly learn the abbreviation.

Not the answer you're looking for? Browse other questions tagged or ask your own question.