CODEWITHSUNDEEP
mysqlrelational-database
puk
puk

Reputation: 16752

How should hierarchical data be structured in mySQL

We are designing a database to keep track of a network including Servers, Switches, and cameras. We have noticed that there is a great deal of inheritance in the table definitions. For example, all switches have an IP address, but some have 48 ports while others only 24, some have SFP ports, some two power supplies, others just one.

I know that whether in normal or OO programming, you prefer to put everything in modules/objects. So it is better to have a class for a DOOR and a HANDLE object, instead of having a DOOR class with variables defining its handle. Is this the same with relational databases.

Here are two ways of describing transcievers. I can either create one table with boolean variables like so

CREATE TABLE IF NOT EXISTS Transceiver
(
   ID INT NOT NULL AUTO_INCREMENT,
   singleMode BOOL,
   multiMode BOOL,
   1Gig BOOL,
   10Gig BOOL,
   1km BOOL,
   10km BOOL,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

Or I can create multiple tables like so

CREATE TABLE IF NOT EXISTS MM1G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM10G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM1G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM10G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM1G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM10G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM1G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM10G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

The problem with relational databases is that you have to use Joins and unions, and if you have 15 different tables, the whole things turns into a nightmare.

In case anyone is wondering, we will later need to generate a Bill of Materials, so we may need to find out the total number of single mode transceivers, or total number of PSUs, or power consumption of all 24 port switches, etc.

EDIT It just occoured to me that there is no hierarchy in my example, however, I could make it go like so:

Upvotes: 0

Views: 50

Answers (1)

reaanb
reaanb

Reputation: 10066

  1. You can combine related boolean columns into a single domain. For example, 1Gig and 10Gig could be combined into a Speed integer column, 1km and 10km into a Range integer column, and if singleMode and multiMode are mutually exclusive, you need record only one of them.

  2. Joins and unions aren't a problem with relational DBMSs, they're some of their best features. The ability to compose relations and derive information from recorded facts is what makes a relational DBMS a useful and intelligent agent that can answer questions rather than a dumb record store that just stores and retrieves bytes.

  3. I rarely have to join 15 tables when a database is well designed, but when I do, it's usually easy and efficient to do so.

  4. Don't confuse object-oriented programming with relational databases or data modeling. OO is great for building state machines which use state. (Relational) data modeling is great for organizing state. The two disciplines are orthogonal.

  5. I see no need for hierarchy in your data.

Upvotes: 2

Related Questions