I use MySQL.
In all my tables, I use the naming convention of the primary key as 'id' and foreign key as 'entity_id'. For example:
Animal
---------
id (PK)
hospital_id(FK)
Now I am implementing the "category" Entity-relationship so I will have the tables of Cat, Dog which is the sub-type of Animal table. So what naming should I use for that single column that is both a PK and FK.
Below is the naming I can think of, suggest if any of this is correct. I want the name of the column as explicit as possible so that anyone looking at the database can know it's a category relationship as fast as possible.
Naming 1:
Animal
---------
id (PK)
Cat
---------
id (PK,FK)
Dogs
---------
id (PK,FK)
Naming style - Follow primary key convention.
pros - Nothing to change.
cons - Doesn't depict the category relationship.
Naming 2:
Animal
---------
id (PK)
Cat
---------
animal_id (PK,FK)
Dogs
---------
animal_id (PK,FK)
Naming style - Follow foreign key convention.
pros - Easy to know it's a foreign key.
cons - Not sure if it's correct.
Naming 3:
Animal
---------
animal_id (PK)
Cat
---------
animal_id (PK,FK)
Dogs
---------
animal_id (PK,FK)
Naming style - Not sure.
pros - Can identify the relationship at first glance.
cons - break my primary key convention.
I refer the design from the accepted solution in Implementing Comments and Likes in database if it helps.
