Should I have one master equipment table or multiple property tables?

Question

I have the following situation in a database of equipment I am trying to design. I currently have 3 tables detailing 3 different types of equipment and 1 table to keep all the ids unique across all 3 tables. As follows:

CREATE TABLE EQUIPMENT
(
    /*Primary key*/
    EQUIPMENT_ID         VARCHAR(30),

    PRIMARY KEY (EQUIPMENT_ID)
);

CREATE TABLE PIPING
(
    /*Primary key*/
    EQUIPMENT_ID         VARCHAR(30), 

    /*Generic Fields*/
    STATUS               VARCHAR(1),
    INSPECTION_STATUS    VARCHAR(1),
    REMEDIATION_STATUS   VARCHAR(1),

    /*Specific properties for piping equipment*/
    OUTSIDE_DIAMETER     FLOAT,
    WALL_THICKNESS       FLOAT,

    /*About another additional 50 specific fields*/

    PRIMARY KEY (EQUIPMENT_ID),
    FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(EQUIPMENT_ID)
);

CREATE TABLE VESSELS
(
    /*Primary key*/
    EQUIPMENT_ID         VARCHAR(30), 

    /*Generic Fields*/
    STATUS               VARCHAR(1),
    INSPECTION_STATUS    VARCHAR(1),
    REMEDIATION_STATUS   VARCHAR(1),

    /*Specific properties for vessel equipment*/
    DISHED_END_THICKNESS FLOAT,
    DESIGN_CODE          VARCHAR(25),

    /*About another additional 50 specific fields*/

    PRIMARY KEY (EQUIPMENT_ID),
    FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(EQUIPMENT_ID)

);

CREATE TABLE TANKS
(
    /*Primary key*/
    EQUIPMENT_ID            VARCHAR(30), 

    /*Generic Fields*/
    STATUS                  VARCHAR(1),
    INSPECTION_STATUS       VARCHAR(1),
    REMEDIATION_STATUS      VARCHAR(1),

    /*Specific properties for tank equipment*/
    FLOATING_ROOF_DIAMETER  FLOAT,
    FLOATING_ROOF_THICKNESS FLOAT,

    /*About another additional 50 specific fields*/

    PRIMARY KEY (EQUIPMENT_ID),
    FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(EQUIPMENT_ID)

);

Is it better to combine all of these tables into one master equipment table with 150 columns? Or leave them separated out in their groups keeping in mind the following:

All fields in the piping, vessels and tanks tables have a 1:1 relationship with and are all dependent on the primary key.

From referring to other posts on the stackoverflow community on this subject...

MySQL: multiple tables or one table with many columns?

It would appear that the best design for this scenario would be one large table with many columns due to the 1:1 relationship between the primary key and the columns but I am not yet convinced and would like someone else's thoughts on the matter.

@Josh there is no definitive answer. Both approaches will work, but which one is better depends on a lot of details specific to your case. — Vatev, Mar 01 '18 at 08:52

score 1 · Answer 1 · answered Mar 01 '18 at 09:32

Some general observations, too long for comment:

EQUIPMENT_ID         VARCHAR(30) -- There's almost certainly no good reason for this not to be an auto-incrementing integer

STATUS               VARCHAR(1) -- CHAR(1) uses less memory and achieves the same thing.

OUTSIDE_DIAMETER     FLOAT -- It seems more plausible to me that this would be a DECIMAL, and likewise for all the other FLOATs

Finally, if you do decide to switch to an EAV model, consider having separate tables for the different data types, so all the DECIMAL type things go in one table, and all the string type things in another.

thanks for the comments i'll implement some of these field changes into my code. — Josh, Mar 02 '18 at 00:39

score 1 · Answer 2 · answered Mar 01 '18 at 14:54

1

There is a technique that is relevant here. It's called Class Table Inheritance. There is more detail here.

answered Mar 01 '18 at 14:54

Walter Mitty

18,205
2
28
58

that article you posted was very informative. I am convinced now that single table inheritance in my case is the way to go. But whether you choose single table of class table inheritance largely depends on the specifics of your application. Can you please update your answer along the lines of this? I will then mark it as a solution! – Josh Mar 02 '18 at 00:28

Should I have one master equipment table or multiple property tables?

2 Answers2