views:

210

answers:

2

I'm trying to design a database for a server and database inventory and I'm looking for a good database design. We have tables for server clusters, stand alone servers and databases. I would like to represent the following relationships in the database:

A one to many relationship from cluster to servers.
A one to many relationship from database to cluster/server.

The difficulty is in the second relationship becuse the clusters and servers are in separate tables and a cluster is made up of servers. What is the best way to represent this relationship?

A: 

Option 1: Have two fields in the database table. One refers to server, the other to cluster. Keep one of them null.

Option 2: Another approach is to add an entry in cluster for each stand-alone server also and link only to that table.

Option 1 is really not the cleanest solution (i do agree with the comments), so go for option 2 :)

tehvan
No no no. This is a TERRIBLE idea.
cletus
I've tried that, but it makes the SQL rather nasty!!
macleojw
@cletus: got any better? it works
tehvan
S.Lott saved me the trouble. His post is right on the money.
cletus
cletus
+3  A: 

It sounds like you have this in your relational view of the situation.

Cluster : name, other attributes of cluster

Server : name, optional FK to cluster, other attributes of a server

Database : name, (FK to cluster OR FK to server)

The issue is that you have a somewhat more complex real-world situation, one that relational technology doesn't reflect cleanly.

Host -- an abstract superclass for places a database can run.

Cluster (extends Host) : name, etc.

Server (extends Host) : name, optional FK to cluster.

Database : FK to Host

You have several choices for handling this kind of "subentity" problem.

  1. Collapse Host, Cluster and Server into a single table. This leads to a recursive relationship among Host (as Cluster) and Host (as Server). This is kind of annoying, but it does create a single table for Host, Cluster and Server. The resulting table has a lot of nulls (Cluster rows use one bunch of columns, Server rows use a different set of columns.) You have to add a column to discriminate among the subentities of Host.

  2. Push Host information down into Cluster and Server. This is useful when you have a lot of common information in the Host table, and very little subclass-specific information in the Cluster or Server tables. The Cluster and Server tables look very similar (essentially clones of Host) with a few columns that are different.

  3. Use a Join between (Host and Cluster) or (Host and Server) based on a discriminator in Host. While fairly complex, this scales well because all Databases are joined to a Host, and the complete list of Hosts is a union of Hosts which join to Server plus Hosts which join to Cluster.

  4. Use optional FK fields in Database. This requires a union between Database joined to Cluster plus Database joined to Server to get a full list of databases. Each Database might have to have a discriminator so that you could distinguish among the various combinations of NULL values in the two FK fields. There are four possible combinations, of which two are sensible, and two might be prohibited. Trying to simply use two nullable FK's doesn't usually work out well, so you often need a status flag to separate Database on Cluster from Database on Server from Database not assigned to anything, from Database with unknown hosting from any other status that might be relevant.

S.Lott
+1 discriminated types ar ethe way to go, particularly if you're using a modern ORM (and even if your'e not).
cletus
The subentity problem is a case of the gen-spec pattern. There are a lot of websites that explain how to express a gen-spec pattern in either Relational modeling or ER modeling.
Walter Mitty