Proper Design for multiple 1 to 0..1 Relationships

Question

I am currently working on an interesting project. I have developed several straight forward databases, however this current setup has me a bit baffled as it has multiple 0:1 relationships and I'm not entirely sure if I am designing the tables and relationships correctly. I've read several SO questions How to Create Multiple one to one's. However, most of these questions are only one relationship deep, though I figure the basics are still the same.

My specs are as follows:

I. Restraint has Type
II. Type: Physical, Chemical, Mechanical
   a. Physical has type
      i. various Holds
   b. Chemical has type
      i. various Medicines
   c. Mechanical has Type
      i. Point has Type
         1. various numbers
     ii. Various devices

Restrictions:

 1. Only one type per restraint
 2. Only one type per Physical Restraint
 3. Only one type per Chemical Restraint
 4. Only one type per mechanical Restraint
    i. If mechanical restraint type is Point only one Point Type per mechanical retraint

This is my current Design

As it shows I have a 1:Many from Restraint to RestraintType then a 0:1 from each of my RestraintType Tables. With this design, so I think, I could update any description or add new types without having to edit the Database. Unless a new Type of Restraint is added that has subcategories.

Is there a better way to design this setup? There is a lot more relationships like this on this project, however, if I can get a good grasp on this, the rest should be easy.

Edit

Here is a second design schema that condenses TypeRestraint lookups. It makes my normalization nerves tingle, but it might be more efficient?

Edit

Here is a more specific view of what is needed.

A restraint occurs. A restraint can be a Physical Restraint, a Chemical Restraint or a Mechanical Restraint. These are types of restraints that are mandated by government reporting.

A Physical Restraint is one of the following: 1-Person Hold, 2-Person Hold, Team Hold

A Chemical Restraint is one of the following: A single medicine at a specific dose or a pair of medicines at a specific dose.

A Mechanical Restraint is one of the following: Gloves, Belt, Chair, x Point Bed Restraint (Where x can be a number from 1 - 5), spit guard and other things that are added and removed periodically.

Physical restraint rarely if ever changes, as it is fairly straight forward. Chemical restraint changes as new drugs are used and old ones are phased out. Mechanical Restraint changes frequently.

A Restraint can only be one Type/Sub-Type

If say there was a 1-Person Hold, 2-Person Hold, Team-Hold then a Chemical Restraint to calm them down. That would be 4 Separate restraints, but that gets off into other requirements of the project.

Answer 1

OK I see the following: Only 2 tables, one has a self join however and a hierarchy)

**Restraint**
RestraintID (PK)
RestraintTypeID (FK)
Description
Dose - only populated if RestraintTypeId is chemical  
  unsure if multiple chemicals are used if we need one for each or 
  if there is only a single "Dose" because the mixture is static.

**RestraintType** (Basically a hierarchical look-up) 
                  (use recursive Common table expressions to query sub-levels if needed)
RestraintTypeID (PK)
ParentID (FK) to RestraintType.RestraintTypeId
Description
Active (Date)
Inactive (date)

data in RestraintType would look like...

1 NULL Physical 
2 NULL Chemical
3 NULL Mechanical
4 1    1-Person Hold
5 1    2-Person Hold
6 1    Team Hold
7 2    Medicine Name
8 2    Medicine Name 2
9 2    Medicine Name 3
10 2   Medicine A & B
11 2   Medicine C & D
12 2   Medicine A & D
13 3   Gloves
14 3   Belt
15 3   Chair
16 3   1-Point Bed Restraint
17 3   2-Point Bed Restraint
18 3   3-Point Bed Restraint
19 3   4-Point Bed Restraint
20 3   5-Point Bed Restraint

This ensures each restraint only allows for one type to be used. and for each type selected, only allows one sub type for physical, Chemical and mechanical. It loosely meets #4 by defining all available point types. This could be managed differently with data using another parent child relationship but I don't see the need. The dates define when each options becomes active/inactive so historical values can be maintained.

I believe this is as simple as it gets and meets all the requirements while maintaining a 4th normal form.

Design gets much simpler as you can display the options in a tree view and allow new leafs and elements to change simply populating new start/end dates behind the scenes. ones started w/o an end date are active, ones with an end date are no longer available to select. System maintains history of restraints used no data from RestraintType is ever Purged. unless all related Restraints are first purged. (I don't know what retention rules you have to follow for this but I suspect there are some)

The hierarchical queries can get a little tricky if you've not done many of them.

We only need to store the LEAF results since each restraint can only have one type and each type can only have one sub/type. Since we know each type/sub type relation, we only need to record the lowest level and can always look-up the hierarchy if needed.

From a UI stand point consider

RESTRAINT INFO BASE INFO....

Restraint Used
(User selects from LOV of top level)
(System queries for next level list) and lets user select from a second LOV
(system queries for next level list) if exists, lets user select from a 3rd level (etc)
We store the last level Id of the Restraint used.  
IF the restraint is a chemical as the top most node, then 
the user must also define a "Dose" field that becomes active.

Where I think you were going wrong:

The sub tables you created were all different types of restraints. They all fit into a single table for restraint types! You just needed a hierarchy to define how they related to each other. The only exception was "DOSE" but since dose relates to an event of a restraint, it belonged either on a separate restraint detail table or could be put on the primary table itself. A separate table would be more inline with a scaling solution; but either works with current defined requirements.