10

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') )

Tesla
  • 793
  • 1
  • 10
  • 22

3 Answers3

7

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-specific1).

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 inheritance2:

enter image description here

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

2 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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
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).

lazarus
  • 371
  • 2
  • 10
  • Yes, I will most likely be using an ORM, which I guess tips it in favour of two different tables? – Tesla Aug 30 '12 at 15:48
2

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))
Kermit
  • 33,827
  • 13
  • 85
  • 121