Design issue
I am currently working on a project where I have "units" that will have three different "phases" and three "steps" for each "phase". I am having problems designing my database layout, resulting in me having no idea if it is worth putting it all in one table or multiple tables.
eg:
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, file_name, unit_id
table 3: step_id, step_name, unit_id
...
Is it easier to constantly update the fields in the row, or is it better to put the id 'units' in other tables?
(also each phase has the same steps)
just to clarify: each block goes through 5 different phases. there are 3 steps in each phase. after the block has passed all the phases, it returns to the resting state. the user starts this process.
source to share
You have to mine the rules of the normal form of the database. This will help you create tables.
Take a look:
Rules:
- Eliminate duplicate columns from one table.
- Create separate tables for each group of related data and define each row with a unique column or set of columns (primary key).
- Delete subsets of data that apply to multiple rows in a table and put them in separate tables.
- Create relationships between these new tables and their predecessors using foreign keys.
- Remove columns that are independent of the primary key.
source to share
http://en.wikipedia.org/wiki/Database_normalization
This is a topic to dive deep into. But for your specific problem: try not to duplicate entries. Try to design your database in such a way that you can relate each table to the others using the least number of common properties (keys).
source to share
'units, which will have three different "phases" and three "steps" to each "phase"
Relationships as I see them:
- units to stages: 1-to-many
- units to steps: 1-to-many
- stages: 1-to-many
I would have three separate tables: units, stages, and stages.
- units: id, category, name
- Stages: id, name, unit_id
- steps: id, name, phase_id
Then, if you want to get all the steps associated with a block, SQL similar to the following will 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'
source to share