Two tables with same columns or one table with additional column?

Question

Say I have two tables (Apples and Oranges) with the same columns and just a different table name. Would there be any advantages/disadvantages to turning this into one table (lets say its called Fruit) with an additional column 'type' which would then either store a value of Apple or Orange?

Edit to clarify:

CREATE TABLE apples ( id int, weight int, variety varchar(255) )

CREATE TABLE oranges ( id int, weight int, variety varchar(255) )

OR

CREATE TABLE fruit ( id int, weight int, variety varchar(255), type ENUM('apple', 'orange') )

Answer 1

Depends on constraints:

• Do you have foreign keys or CHECKs on apples that don't exist on oranges (or vice-versa)?
• Do you need to keep keys unique across both tables (so no apple can have the same ID as some orange)?

If the answers on these two questions are: "yes" and "no", keep the tables separate (so constraints can be made table-specific¹).

If the answers are: "no" and "yes", merge them together (so you can crate a key that spans both).

If the answers are: "yes" and "yes", consider emulating inheritance²:

---

¹Lookup data is a typical example of tables that look similar, yet must be kept separate so FKs can be kept separate.

² Specifically, this is the "all classes in separate tables" strategy for representing inheritance (aka. category, subclassing, subtyping, generalization hierarchy etc.). You might want to take a look at this post for more info.

Answer 2

If there really is not any further business rules (and resultant underlying data requirements) that separate the two sub-types then I would use one table with an fk to a FruitType lookup table.

You dont mention what you will be using to access the schema which may affect which approach you take (e.g. if you are using a platform which provides an ORM to your database then this may be worth noting).

Answer 3

The advantage would be normalization. Your tables would then be in 2NF (second normal form). Your fruit type would be a foreign key to a table with those fruits like so:

CREATE TABLE fruit_type (type varchar(15))

CREATE TABLE fruits (id int, weight int, variety varchar(255), type varchar(15))