Mysql – How to design this simple DB

MySQLrelational-theory

How to design this simple DB?

I need to add some things to a DB I use to keep track of servers.

Table looks something like this.

Matrix
| Name        | Description    | Etc  | Etc    | 
------------------------------------------------
| Server01    | First Server   | Data | Data   |

I want to add a table called Updates. It should look something like this:

Updates
| Name        | Q1 Updates | Q2 Updates | Q3 Updates |
------------------------------------------------------
| Server01    | Done       | Incomplete | Incomplete |

What is the best way to "link" these tables? I need everything in the Name field in Matrix to be in the Updates table as well. I'm a fundamental noob when it comes to databases, so I need some 101 help with this.

I have some triggers set up for another table that keeps track of other information and that works very well… I could just add more triggers for the table I am wanting to create… but I'm not sure if that's the best method.

What would you do?

EDIT: I want the Name field from Updates to always be an exact replica of what is in Matrix.

Best Answer

I'd probably start with something like this:

create table Matrix (
    id           integer primary key auto_increment,
    Name         varchar(30) not null,
    Description  varchar(30) not null
);

create table Updates ( -- Note, singular not possible, conflicts with keyword 'update'.
    id      integer primary key auto_increment,        
    Name    varchar(20) not null
);

create table UpdateStatus (
    id      integer primary key auto_increment,        
    Name    varchar(20)        
);

create table Matrix_Update (
    id_Matrix        integer not null,
    id_Updates       integer not null,
    id_UPdateStatus  integer not null,
    --
    foreign key (id_Matrix      ) references Matrix      (id),
    foreign key (id_Updates     ) references Updates     (id),
    foreign key (id_UpdateStatus) references UpdateStatus(id)
);

You then fill your Matrix entries with 

insert into Matrix (id, Name, Description) values ( 1, 'Server01', 'First Server');
insert into Matrix (id, Name, Description) values ( 2, 'Server02', 'Second Server');

Similarly, the Updates are filled like so

insert into Updates (id, Name) values ( 1, 'Q1 Update');
insert into Updates (id, Name) values ( 2, 'Q2 Update');
insert into Updates (id, Name) values ( 3, 'Q3 Update');

Finally, insert the possible Update Stati

insert into UpdateStatus (id, Name) values (1, 'Done');
insert into UpdateStatus (id, Name) values (2, 'Incomplete');

Now, you have the framework to assemble your "configuration":

insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 1, 1);
insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 2, 2);
insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 3, 2);

This "configuration" can then be queried with a pivot query:

-- Pivot Query
select 
  Matrix.Name                                                MatrixName,
  Matrix.Description                                         MatrixDescription,
  group_concat(if(Updates.id = 1, UpdateStatus.Name, null )) Status1,
  group_concat(if(Updates.id = 2, UpdateStatus.Name, null )) Status2,
  group_concat(if(Updates.id = 3, UpdateStatus.Name, null )) Status3
from
  Matrix_Update                                                                 join
  Matrix               on Matrix_Update.id_Matrix= Matrix.id                    join
  UpdateStatus         on Matrix_Update.id_UpdateStatus       = UpdateStatus.id join
  Updates      Updates on Matrix_Update.id_Updates            = Updates.id
group by 
  Matrix.Name,
  Matrix.Description;

I want the Name field from Updates to always be an exact replica of what is in Matrix.

With this design, this is no problem, since the Name is not stored redundantly.

Related Question