Relational DB design - Need Help

[twofive]

I've contructed a fairly simple normalized (3NF), relational database. By simple, I mean each table relates to other table(s) in a 1 to many (1,n) relationship. Now, I am trying to extend this database, but I'm not sure how to proceed given specific qualifiers. Allow me to illustrate:

* 1 Pod has 4 Channels > (1,4) relationship.
* 1 Sensor may occupy 0, 1 or 2 Channels > (0|1|2) relationship.
* 1 Pod has between 0 and 4 Sensors > Indirect relationship between Pod and Sensors. Direct relationship is between Channels and Sensors.
* 1 Sensor connects to 0 or more Pods, but only 1 Pod at any given time. Indirect Many-to-many relationship between Pod and Sensor. Again, the direct relationship is between Channels and Sensors.

I've drawn up a db diagram and attached. PodSernsorAssociation allows a many to many relationship between Pod and Sensor, although it may not be correct to even have this relationship.

Please let me know your thoughts!

 

[twofive]

Edit:
* 1 Sensor may occupy 1 or 2 Channels > {1|2} relationship.

The correct cardinality is {1|2}, not {0|1|2}. This is correct in the attached image.

 

[twofive]

Bump. Any ideas?

 

[r937]

what was the question?

you're trying to extend this database, but you're not sure how to proceed

extend it how?

r937.com | rudy.ca

 

[twofive]

I've figured this one out after hitting the books. Sorry to not be more specific, but what I meant by "extending" was that I already have a database w/ several tables, and that I'm adding to it. To be succint, I didn't want to name all the tables that didn't relate to the "new" tables I was trying to add.

Basically, (1) I got rid of the Pod-Sensor relation, since the relation is really Pod-Channel-Sensor, (2) established a many-to-many Channel-Sensor relationship using three tables: Channel-ChannelSensorAssociation-Sensor, (3) and to make it so a Pod always has four Channels, I set up a Trigger.