MySQL database schema for relational system data

Question

Let's assume there are systems. systems contains subsystems. subsystems contains devices. devices contains measurementpoints.

Lets assume an Webinterface /get/systems where all the systems related to a user are returned.

Now let's say users can have their own visibility on the systems. So when user A gets his systems it is another set - based on visibility rights - than user B gets.

The schema

CREATE TABLE system( sid INT NOT NULL AUTO_INCREMENT, cid INT NOT NULL, PRIMARY KEY(sid) ); CREATE TABLE subsystem( subsid INT NOT NULL AUTO_INCREMENT, sid INT NOT NULL, PRIMARY KEY(subsid) ); CREATE TABLE device( did INT NOT NULL AUTO_INCREMENT, subsid INT NOT NULL, PRIMARY KEY(did) ); CREATE TABLE mspot( mid INT NOT NULL AUTO_INCREMENT, did INT NOT NULL, PRIMARY KEY(mid) );

For a complete set of systems, subsystems, devices and measurementpoints it's a join within these tables. If it shall be extended to visbilitys, what is a viable approach?

First shot was to create a table like

CREATE TABLE permittedsystems ( sid INT NOT NULL, uid INT NOT NULL, PRIMARY KEY(sid, uid) );

(permittedsubsystems, permitteddevices and permittedmspoints are not showed here)

One example query of getting a users system is

SELECT * FROM customer JOIN user ON customer.cid = user.cid JOIN system ON system.cid = customer.cid LEFT JOIN subsystem ON system.sid = subsystem.sid LEFT JOIN device ON device.subsid=subsystem.subsid LEFT JOIN mspot ON mspot.did = device.did JOIN permittedsystems ON permittedsystems.uid = user.uid AND permittedsystems.sid = system.sid JOIN permittedsubsystems ON permittedsubsystems.uid = user.uid AND permittedsubsystems.subsid = subsystem.subsid JOIN permitteddevices ON permitteddevices.uid = user.uid AND permitteddevices.did = device.did JOIN permittedmspots ON permittedmspots.uid = user.uid AND permittedmspots.mid = mspot.mid WHERE user.uid = 1;`

Any alternatives?

Thanks in advance

What do you mean with "huge joins"? A join with five tables? I would not call that "huge". Please post your queries. Moreover, please edit the code part on CREATE TABLE mspot. — Renzo
– Renzo, Commented Jul 3, 2015 at 5:48

user3538411 · Accepted Answer · 2015-07-03 07:51:01Z

Typically when you have a zero to many relationship, such as users with devices, you represent those relationships in another table with foreign keys to each. So you would have a table of user IDs and device IDs. You could query this table

Select * from UserDevices LEFT JOIN Device ON device.did = UserDevices.did Where UserID = '1'

This is the classical method of representing a 1 to many or a zero to many cardinality in your schema.

Your data set would also lend its self well to an OLAP approach. Using a star schema, you could create a table with user IDs and all device, system, ect ids associated with that user. You could then join the fact table with any number of dimension table, increasing performance by limiting the size of the query set in your where and on clauses. Joining a lot of tables together is much less performance expensive if you can first decrease the size of the tables before you join them.

Select * From fact_table Left Join System ON System.did = fact_table.did Left Join Device ON Device.did = fact_table.did --ect Where fact_table.UserID = '1'

This requires a lot of work to reorganize your data however, and if users can have any number of relationships with your dimension tables it can make creating a fact table very difficult.

Let's say i want to use a star schema. i would create a table like CREATE TABLE syspermission ( uid INT, //FK sid INT, //FK subsid INT, //FK did INT, //FK mid INT, //FK ); ? Question comes to my mind is: What if a subsystem has no devices or a device has no mspots?
You can have nulls in a fact table. Your join then would produce no results, ie null in every column.

Collectives™ on Stack Overflow

MySQL database schema for relational system data

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related