Clever Geek Handbook

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.

+2


source to share


4 answers


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.
+2


source


If a unit cannot be more than one phase or step at the same time, or the phases and stages are unique for each unit, having multiple tables is completely meaningless.



+1


source


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).

0


source


'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'
0


source






More articles:


All Articles