How to design this simple DB?

Question

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.

Answer 1

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.

Answer 2

Based on what you've got here, it looks like you should be able to JOIN Matrix and Updates together based-on the "Name" field. If you wanted to see all servers from Matrix that had updates, you could to something like this:

SELECT m.Name, m.Description, u.*
FROM Matrix m
INNER JOIN Updates u ON u.Name = m.Name;

That's based-on a few assumptions:

• "Name" is your primary key in both tables.
• "Name" is unique in the Updates table (no server would have more than one entry).

"What would you do?"

If it were me, I would create an Updates table that looked something like this:

table: Updates
Name varchar(16)
TimeFrame varchar(3)
Status varchar(10)

Row data would look like:

name     TimeFrame Status
Server01 Q1        Done
Server01 Q2        Incomplete
Server01 Q3        Incomplete 

It would have a composite (primary) key of Name and TimeFrame to ensure uniqueness (because in this scenario, "Name" will be repeated).

The advantage here, is that if your time frame for updates is altered/increased at any point (ex: if you start doing updates every 2 months, instead of quarterly) you have the flexibility of adding more updates without having to add a column to the Updates table. Of course, I'm saying that without knowing what the rest of the fields on Updates are, so maybe that wouldn't make sense. Or there could be other opportunities for normalization/optimization as well.

Answer 3

How about this?

CREATE TABLE Matrix
(
    Name        NVARCHAR(256) NOT NULL,
    Description NVARCHAR(1000) NULL,
    Etc         DATATYPE,
    .
    .
)

CREATE TABLE Updates
(
    Name            NVARCHAR(256) NOT NULL,
    UpdateType      VARCHAR(25) NOT NULL, -- Q1 updates, Q2 Updates ...
    UpdateStatus    VARCHAR(25) NOT NULL, -- Done, Imcomplete, InProgress...
)

ALTER TABLE Updates ADD CONSTRAINT FK_Updates_Matrix
FOREIGN KEY (Name) REFERENCES Matrix (Name);