What are the more readable naming conventions for lookup tables?

Question

We always call search tables - for example, Countries, Cities, Regions ... etc. - as shown below:
EntityName_LK OR LK_EntityName (LK_ Countries OR LK_Countries)
But I ask, does anyone have more efficient name conversions for lookup tables?

Edit :
We are thinking of making a postfix or prefix for the solution as a conflict:
if we have User tables and a lookup table for UserTypes (ID-Name), and we have many to many relationships between User and UserTypes that make us a table, which we can call Types_For_User , which can lead to confusion between UserTypes and Types_For_User So, we would like the UserTypes lookup UserTypes be like UserTypesLK obvious to everyone

Answer 1

Before you decide that you need a “lookup” moniker, you should try to understand why you designate some tables as “lookups” and not others. Each table should represent an entity for itself.

What happens when a table that has been designated as “search” grows in scale and is no longer considered a “search”? You either remain with a table name change, which can be burdensome, or leave it as it is, and also explain to everyone that this table is not a “search”.

Common scenario mentioned in the comments on the join table. For example, suppose a user can have several “types” that are expressed in a join table with two foreign keys. Should this table be called User_UserTypes ? In this case, I would say that I prefer to use the suffix Member in the join table. So we would have Users , UserTypes , UserTypeMembers . Secondly, the word "type" in this context is quite general. Does UserType mean a role? The term you use may matter. If UserTypes are really Roles , then our table names become Users , Roles , RoleMembers , which seems perfectly clear.

Answer 2

Each table can become a lookup table. Please note that the person is in the invoice table. Therefore, in my opinion, tables should simply be called the (only) name of the object, for example. Person invoice.

What you really want is the standard for column names and constraints such as

 FK_Invoice_Person (in table invoice, link to person) PersonID or Person_ID (column in table invoice, linking to entity Person) 

At the end of the day, it all depends on personal preferences (if you can leave with dictation) or team standards.

updated

If you have searches related only to objects, such as Invoice_Terms, which is a search from a list of 4 scenarios, you can name it as Invoice_LK_Terms, which will make it appear by name, grouped by account. Another way is to have one lookup table for simple unambiguous searches, separated by a function (table + column), for which, for example,

 Lookups Table | Column | Value 

Answer 3

Here are two problems: use a prefix or suffix.

• In a sorted list of tables, do you want the LK tables to be together, or you want all the tables related to EntityName to appear together

• When programming in autocomplete environments, you probably want to enter "LK" to get a list of tables or the beginning of EntityName?

I think there are arguments for both, but I would decide to start with EntityName.

Answer 4

There is only one type of table, and I don’t think there is any good reason for calling lookup tables. Use a naming convention that works the same for each table.

Answer 5

In one area where table naming conventions can help, this is data migration between environments. We often have to move data in search tables (which limit the values ​​that may appear in other tables) along with schema changes, as these lists of valid values ​​change. We do not currently name the search tables in different ways, but we are considering this to prevent the migration guy asking, "which tables look again?" everytime.