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.


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:

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

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

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

  ID (PK)
  (other fields)

The data for this schema could look like this:

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

ID | Name
100| Big garden
101| Little garden

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

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:

  ID (PK)
  garden_ID (FK to garden.ID)
  gardener_ID (FK to gardener.ID)

No I think there is no such need. You just create a new table Garden which will have its own primary key & it should contain foreign key of the Gardner. So that you can maintain the relationship between these tables & can fetch data using SQL. Be aware about inserting & deleting data with foreign keys.


Your model above works for a list of flowers where the garden is not relevant. But it wouldn't work when flowers or gardeners could be in multiple gardens.

You should have a table for each type of entity. You don't have a garden table, you have a flower table, so to complete the model you need to add a Garden table and link Flower table to it.


GardenerID | Name   | Garden
1          | Bob    | 1
2          | James  | 2
3          | Ian    | 3

FlowerID   | Type   | Garden
1          | Holly  | 1
2          | Lilach | 2
3          | Larch  | 3
4          | Larch  | 2

GardenID   | Address
1          | 221b Baker St
2          | 29 Acacia Rd
3          | 10 Downing St

Of course where the foreign keys are depends on how you want to define the relationships and whether you want multiple gardeners to a garden or vice versa.

  • I have updated the original question slightly to clarify further. I am assuming that each gardener will only be working in one garden, and does not move around. The Garden table lists everything that is in that garden. Oct 30 '13 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.