Is it good practice to create new tables for new elements in a database

best practicesdatabase-design

I am trying to create a good model for a database that I need for an application. The way that I have tought about realizing it involves creating a new table for each new element that is added, but I am unsure if that really is such a good idea. I found a related question that used a garden as an analogy, and I quite liked that, so I will try to exemplify using that below. Note that each gardener only works with one garden. The gardeners doesn't need to be able to be at several gardens.

I have a set of Gardereners as such:

PrimaryKey | Name | Garden
1          | Bob  | Garden1
2          | James| Garden2
3          | Ian  | Garden3

Each Garden will point to a new table containing the flowers. The Garden contains all the flowers in that garden.

FlowerNumber | Type
1            | Holly
2            | Lilach
3            | Larch

The question is then, would it be "right" to create a new table Garden for each new Gardener that is created? I tought it kind of wasteful to create new tables all the time, but I am not sure of any other ways to do it. I think object-oriented thinking might have perverted my way of thinking about databases.

Best Answer

No. This will cause maintenance problems: when you want to update the structure of Garden, then changes (adding a new column, removing an old column, changes to constraints, changes to triggers, whatever) will have to be propagated to every Garden_* table. It is also bad data modelling. You should aim to have one table per entity/relation in your system. Having multiple Garden tables would imply that they are fundamentally different.

I'd suggest a structure more like this:

Gardener
--------
  ID (PK)
  Garden_ID (FK to Garden.ID)
  (other fields such as name, contact info, ...)

Garden
------
  ID (PK)
  (other fields such as name, garden address, ...)

Flower
------
  ID (PK)
  Type_ID (FK to FlowerTypes.ID)
  Garden_ID (FK to Garden.ID)
  (other fields)

FlowerType
----------
  ID (PK)
  name
  (other fields)

The data for this schema could look like this:

Gardner
-------
ID | Name  | Garden_ID
-----------|----------
1  | Bob   | 100
2  | Sally | 101


Garden
------
ID | Name
---------
100| Big garden
101| Little garden


Flower
------
ID  |  Type_ID  | Garden_ID
----|-----------|----------
1   |  200      |  100
2   |  201      |  100
3   |  202      |  101

Flower_type
-----------
ID   | Name
-----------
200  | Lilly
201  | Hosta
202  | Rose

This will let a gardener be assigned to one garden. If you need more flexibility such that gardeners can be assigned to multiple gardens, and gardens can have multiple gardeners assigned to them, you'd need a separate garden assignment table, and remove the garden_id field from gardener:

garden_assignments
------------------
  ID (PK)
  garden_ID (FK to garden.ID)
  gardener_ID (FK to gardener.ID)
Related Question