Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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)
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

Eg.

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.

share|improve this answer
    
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. –  user1049697 Oct 30 '13 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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