Database convention that prevents confusions

Don't do it like this:

Order:
    OrderId     PK
    Date
    Description



LineItem:
    LineItemId  PK  
    OrderId     FK
    ProductId   FK
    Qty
    UnitPrice
    Amount

To the uninitiated, the LineItem table will look like an standalone table, wherein OrderId will seems like a ProductId column too. Meaning, LineItem's OrderId seems something that can be changed by the user (e.g. via dropdown). Where in actual application, the OrderId on LineItem is something that is set by the app and not being set by the user, not visible to user.



To emphasize that the LineItem only makes sense within the domain of Order and is not a standalone table, and consequently emphasizing that the LineItem's OrderId is not a user-selectable value, move the OrderId before the LineItemId primary key. That is, any foreign key that is before the primary key, should be treated as immutable foreign key.


LineItem table will now look like this:

LineItem:
    OrderId     FK
    LineItemId  PK  
    ProductId   FK
    Qty
    UnitPrice
    Amount             

Placing the immutable foreign key before the primary key is also especially useful when you have a third table(e.g. Preference) on which the defined relationship is not readily apparent. For example:

Product:
    ProductId       PK
    ProductName
    
    
Reseller:
    ResellerId      PK
    ResellerName 
    

Preference:
    PreferenceId    PK
    ProductId       FK
    ResellerId      FK
    PreferenceLevel

If you have a third table that has an unclear name and renaming the table is not an option (e.g. brownfield projects), you can at least move the owning FK before the primary key to emphasize the ownership of Preference table.


For example, if you are a supplier of products and you want to keep track your preferred resellers on each of your product, it's good to structure your Preference table like this:

Preference
    ProductId       FK
    PreferenceId    PK
    ResellerId      FK
    PreferenceLevel

In that case, moving the ProductId before the PreferenceId primary key indicates that the owning entity of the Preference table is the Product table. Hence the PreferenceLevel on that table design, indicates your most preferred resellers for your given product down to the least preferred.



Likewise, if you move the ResellerId before the PreferenceId primary key:

Preference
    ResellerId      FK
    PreferenceId    PK  
    ProductId       FK
    PreferenceLevel

The PreferenceLevel value on that design indicates the most preferred products by a given reseller to buy from you, down to their least preferred. The owning entity on that Preference table is the Reseller


This is just one of the many simple database conventions you can apply to your database, and it can surely help anyone who will read and need to understand your domain entities' relationships.




Lastly, if you have a third table like this:

BookAuthor:
     BookAuthorId
     BookId
     AuthorId

It can also be named as AuthorBook:

AuthorBook:
     AuthorBookId
     AuthorId
     BookId

The third table can be named either way, this is usually called a junction table, this defines a many-to-many relationship, there is no one single owner of the junction table. The junction table is owned by both Book and Author entity. And the columns can be arranged either way too.


To emphasize that there is no single ownership of the junction table. Design your own convention to emphasize non-single ownership. First you can define the third table primary key be the first column of the table, and the two other columns as unique composite. You can also designate some naming convention, e.g. you can name the table like this:

Author_assoc_Book:
     Author_assoc_Book_Id
     AuthorId
     BookId

It's ok to give name your junction table a weird name, as junction tables without payload are usually hidden and not mapped to your ORM classes. On the example above, we put the word assoc between Author and Book, you can choose your own naming convention to emphasize the junction-ness of the table, just be consistent on the naming convention. On the order of entities on the junction table name, since there is no one single owner of the junction table, we can opt to just arrange the name of the two tables on the junction table name based on their alphabetical order to emphasize non-sole-ownership. So Author comes before Book, giving the junction table a name of Author_assoc_Book, not Book_assoc_Author. Likewise with the their columns arrangement, it's alphabetically arranged too, hence AuthorId comes before BookId.


Happy Coding! ツ