0

I have a table called Order which holds customer orders.

I want people to be able to add notes to orders. Whats the best way to name this new table? Because other tables may also have notes e.g. Customer, I need the table to be named so that is shows association with the relevant table. Relationship is an Order will have 0-or-many Notes.

So should I name it:

  1. Order_Note
  2. OrderNote

They all seem fine. I also need to create another table that will list the 'types' of Order that have been placed. An Order can have 1 or many 'types'. So how would I name this association in the table name?

  1. Order_Type
  2. OrderType

In this Order_Type table, it will have just two columns OrderID and TypeID. So I need a final table which holds all possible Types of order that can be placed. How do I name this, given that it will be confusing with the table above?

  1. Order_Types
  2. OrderTypes

But this is breaking the rule of not having plurals in table names.

Edit: The Order table is a data table. Order_Type is a joining table. And finally, OrderTypes is a lookup table. Thanks to Hogan for making this obvious to me. I have also removed hyphenation between words as an option as it may cause future problems.

SOLUTION 1:

  1. Name association between tables using underscore e.g. Order_Type
  2. Name lookup and data tables without underscores e.g. Order, OrderType

I'll also use a schema so that lookup tables show like Lookup.OrderType which helps to clarify what is what.

volume one
  • 6,800
  • 13
  • 67
  • 146
  • This question cannot be answered as there is "no correct answer". Depends on your current code-base (or code-style book). For example, I'd always stick with the 3rd option, for question three I'd answer plural names are okay if it makes things easier to understand. – Matten Oct 24 '13 at 13:53
  • It's really down to personal preference (or your coding convention). I don't like hyphens and underscores in table names but that's just me. – Gareth Oct 24 '13 at 13:54
  • 1
    I think a good case could be made for `order_note`, but opinions will vary. – Bohemian Oct 24 '13 at 13:57
  • 1
    Just to throw something else out there, if your table holds order**s**, why is it called `Order`? A table is a set. In this case, a set of ... order? No, it's a set of order**s**. I'm not a big fan of using OO-based mentality on table names. Unless the table is meant to hold exactly one row, it's plural. But, like underscores etc. this is a preference. You just need to have a good reason for your preference, and be consistent. As an aside, avoid dashes in names because they make your scripts brittle. – Aaron Bertrand Oct 24 '13 at 14:04
  • @AaronBertrand it is not advisable to use plurals in table names. This is best practice I believe. – volume one Oct 24 '13 at 14:13
  • @volumeone "you believe"? Can you explain why it is "not advisable"? Again, it's an opinion thing, but if you believe strongly in something, you should also be able to explain why. – Aaron Bertrand Oct 24 '13 at 14:16
  • http://stackoverflow.com/posts/5841297/revisions – volume one Oct 24 '13 at 14:20
  • You're pointing me to the revisions of someone else's opinion-based answer? Ok. Thanks for the thoughtful response. *shrug* – Aaron Bertrand Oct 24 '13 at 14:23
  • No it explains the reasoning why plurals are not advised. If you want to use them then fine, but it definitely makes sense to me not to use them. – volume one Oct 24 '13 at 14:30
  • @volumeone - That is a horrible reason. This implies that `i` is a good variable name because it is short -- in fact it is a horrible variable name because it is short. – Hogan Oct 24 '13 at 14:48

1 Answers1

1

The way I've done it is listed below

As a side note, these suggestions have to do with how to think about joined table names and it does not matter if you use camel case or not, underscores or not etc.

The important note here is that there is fundamentally a difference between a joining table, a lookup table and a data table. The names should reflect this and be consistent.

1)

I would make a note table and call it Note. Then I would add a relationship between orders and notes and call it order2note or orderNote or orderNoteRel

This table name defines the two joined tables in some order, sometimes you can put the non-FK first but in many cases it is best to just default to alphabetical.

2)

For tables that define a code (or a type as you put it) I will make a convention of ending the "Type" or "Code" or "CD" etc in the table name.

so orderType or orderCD or orderCode would be for the table that defines order types.

3)

The final table is actually a join between order table and orderType table so it would be

order2orderType or orderOrderCD or orderOrderCodeRel

(or some other combination of the conventions I've shown.)

This is the important one. If you remember that the table you are joining to should have order in its name (it is the orderType table) Then the join between order and order type should have order twice in its name. While this seems redundant at first once you get used to it it makes total sense.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • This is interesting, but seems more complex for a human to understand. I'm trying to get the tables names so that they are different enough for a human to understand what table does what, but at the same time showing association between tables. – volume one Oct 24 '13 at 14:01
  • 2
    @volumeone - ah, your naivete is cute. This is simple and easy to understand because it is consistent. Not having a naming convention that handles these cases will make a project that is very hard to maintain. That is why you asked this question right? There is no simpler magic answer. – Hogan Oct 24 '13 at 14:11