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.

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

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

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

  • What is the relationship between Matrix and Updates? From what I'm understanding, is it correct to say that one Matrix can have many updates? – Aaron Dec 27 '11 at 20:57
  • Matrix is a table name. There are hundreds of servers in it. The example I gave only shows 1 server and a cropped set of fields that accompany it. The relationship between the tables, if I am using that word properly, is the "Name" of the server. – AaronJAnderson Dec 27 '11 at 21:05
  • Should I just add the fields to my main table "Matrix" and not create a separate table for this? – AaronJAnderson Dec 27 '11 at 21:17

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

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.

  • This is pretty good. I'm not sure it's totally compatible with the web applications I'm using (PHP Maker and AppGini) but that's not your fault. Great code that I'm saving in my local repo. – AaronJAnderson Dec 28 '11 at 20:32

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.

  • +1 for normalizing the time columns. Adding a column for every new quater would be a maintenance pain! – Anon246 Dec 27 '11 at 23:17
  • I don't think we're on the same page; but I like your answer. A little background to help clarify how I use the data. The "Matrix" is my master inventory of systems. When it's time to update code and install updates I have to do maintenance to a certain subset of servers. (ie, anything with "pr" in the name) - So I want to head over to this other table we're talking about to see what work needs to be done. I'd like it to be pre-populated with server names so I know which ones need to be updated. Does that make sense? – AaronJAnderson Dec 28 '11 at 0:18
  • 1
    So, what I'm saying is that under no circumstance should a system exist on Matrix, and NOT on "Updates" or whatever we decide to call this thing. – AaronJAnderson Dec 28 '11 at 0:35

How about this?

    Name        NVARCHAR(256) NOT NULL,
    Description NVARCHAR(1000) NULL,
    Etc         DATATYPE,

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.