Design Question

Question

i am currently working on a project where i have 'units' that will have three different 'phases' and three 'steps' to each 'phase'. I am having trouble designing the layout of my database whereby i dont not know whether to put it all in one table or multiple tables.

for instance:

table 1: unit_id, unit_category, unit_name, unit_phase, unit_step

or:

table 1: unit_id, unit_category, unit_name

table 2: phase_id, phase_name, unit_id

table 3: step_id, step_name, unit_id

...

is it easier to constantly update fields in a row, or is it better to place the 'units' id in other tables??

(also, each phase has the exact same steps)

to clarify: each unit goes through 5 different phases. within each phase is 3 steps. once the unit has gone through all of the phases, it returns to a rest state. the user is the one who starts the process.

Answer 1

You should loot at database normal forms rules. That will help to design your tables.

Look at:

• Database Normalization Basics
• Rules of DB normalization
• DB normalization

Rules:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.
• Remove columns that are not dependent upon the primary key.

Answer 2

Unless a unit can be in more than one phase or step at the same time, or phases and steps are unique to each unit, having several tables makes absolutely no sense.

Answer 3

http://en.wikipedia.org/wiki/Database%5Fnormalization

This is the topic you should dive into deeply. But for your specific problem: try not to duplicate records. Try to design the database in a way in which you link each table to the others through the smallest number of common properties (keys).

Answer 4

'units' that will have three different 'phases' and three 'steps' to each 'phase'

The relationships, as I see them:

• units to phases: 1-to-many
• units to steps: 1-to-many
• phases to steps: 1-to-many

I would have 3 separate tables: units, phases, and steps.

1. units: id, category, name
2. phases: id, name, unit_id
3. steps: id, name, phase_id

Then, if you wanted to get all the steps associated with a unit, SQL akin to the following would work:

SELECT steps.*
FROM units
LEFT OUTER JOIN phases ON units.id=phases.unit_id
LEFT OUTER JOIN steps ON phases.id=steps.phase_id
WHERE units.id='the particular unit ID for which you want to query'