Entity Relationship Multiple 1:1's

Question

I have an application where I have a generic object (table) called Hull. Each hull in the table is unique.

I have another object that has three hulls, but they are specifically the Port_Hull, Center_Hull and Starboard_Hull.

Rather than create a One to Many relationship, I was trying to create a one to one relationship for each one, but this results in numerous errors unless I make the relationship from Hull to Vessel one to many (which it is not). Any idea how I go about this, or should I abandon the concept and make the vessel to hull relationship one to many and deal with lists that always have three entries?

p.s. Using uniqueidentifiers as many users can be adding records while disconnected.

Hull Table

• HullID uniqueidentifier (primary key)
• plus bunch of hull data fields

Vessel Table

• VesselID uniqueidentifier (primary key)
• MainHullID uniqueidentifier (tried as key and non-key)
• PortHullID uniqueidentifier
• StarboardHullID uniqueidentifier
• plus bunch of Vessel data fields

Answer 1

Are you asking whether it is possible to create this or whether you ought to create this?

First, it is possible:

Create Table Vessel
(
    VesselId uniqueidentifier not null primary key
    , MainHullId uniqueidentifier not null
    , PortHullId uniqueidentifier not null
    , StarboardHullId uniqueidentifier not null
    , ...
    , Constraint FK_Vessel_Hull_Main
        Foreign Key ( MainHullId )
        References Hull( HullId )
    , Constraint FK_Vessel_Hull_Port
        Foreign Key ( PortHullId )
        References Hull( HullId )
    , Constraint FK_Vessel_Hull_Startboard
        Foreign Key ( StarboardHullId )
        References Hull( HullId )
    , Constraint UC_Vessal_MainHullId Unique ( MainHullId )
    , Constraint UC_Vessal_PortHullId Unique ( PortHullId )
    , Constraint UC_Vessal_StarboardHullId Unique ( StarboardHullId )
)

A 1:1 relationship is created when you have a child table with a foreign key to a parent table and the foreign key is required to be unique.

Now, as to whether it is a good design, that would depend on the problem domain. It seems odd to have a vessal with three different hulls based on left, right and center but maybe I'm missing something.

Answer 2

You can solve this 1:1 in two different ways:

1. Add a unique constraint for each invidivual Hull field in Vessel, i.e. MainHull, PortHull, StarboardHull. This will ensure that a Hull can only be used by one Vessel.
2. Remove the Hull fields from vessel, and add a new field to Hull - Vessel. This then explicitly names the vessel that this hull belongs to. It would also seem to make sense to then add a HullType to Hull, so you know what type of hull it is. A unique constraint on HullType+Vessel will ensure that each vessel gets at most one hull of each type (but unfortunately, can have 0 of a given type.)

I would go for the first one. since it seems more natural to select a vessel and then find the associated hulls, and also ensures that each vessel has the 3 hulls required (assmining a non-null constraint on MainHull, PortHull, StarboardHull.)

EDIT: Seeing your comment, and given that vessels don't need 3 hulls, then the second solution is also worth considering. If you need to add additional types of hull, you do this without changing your schema, since the hull type is not inferred from the field it is referenced, but named explicitly in the proposed 'HullType' field.