Database design with multiple nested tables

Question

I have a question concerning table design. I have a solution that should work in my opinion but doesn't.

Consider having two Entities "Subject" and "Process", both with certain attributes. Every "Subject" may be associated with multiple "Processes". Depending on which "Process" is chosen there is a varying number of the entity "Process-Property". In other words when the User associates a "process" with a "subject" he should only be able to edit the "properties" specifically linked to it.

Ultimately I want the User to be able to do 3 things:

1. Creating new "Processes" and specifying the "Properties" asscociated with it
2. List all "Processes" of a certain "Subject" even if there are no "Properties" linked to it
3. Associate a "Process" with a "Subject" and only allow the predefined "Properties" to be evaluated

So the table design should be something like:

• tblSubject={SubjectID,...}
• tblProcess={ProcessID,...}
• tblProcessProperty={PropertyID,...}
• tblRelationProcessProperty={RelationProcessPropertyID, ProcessID, PropertyID}
• tblRelationSubjectProcessProperty={RelationID, RelationProcessPropertyID, SubjectID, PropertyValue}

This does obviously work as long as there is a "Property" associated with every "Process". So my mistake is not to directly link "Subject" to "Process" but then I can't get the table design straight.

Any help is appreciated.

Answer 1

It looks to me like your trying to implement a sort of EAV (Entity-Attribue-Value) design.

Your table seems ok, but this design inherently requires complicated SQL.

There are different methods of doing this but based on your tales above I would go with something like this.

   Subject --< Process --< RelationshipProcessProperty >-- Property

Your property would simply look like this:

    "Property"
    PK PropertyId
    Name

Your RelationshipProcessProperty could look like this:

    "RelationshiipProcessProperty"
    PK RelationshipProcessProperty
    FK Process
    FK Property
    Value

Your SQL is where it would get complicated. Doing a 'generic' design like this has it's impliations as you're looking for multiple values in the same table.

    ; with Property1 as(
    SELECT
    proc.Id as ProcessId,
    prop.Name,
    rrp.Value
    FROM Subject s
    LEFT JOIN Process proc
    ON s.SubjectId = proc.SubjectId
    LEFT JOIN RelationshipProcessProperty rpp
    on proc.ProcessId = rpp.ProcessId
    LEFT JOIN Property prop
    on rpp.PropertyId = prop.PropertyId
    WHERE
    s.Name = "Subject1"
    AND
    proc.Name = "Process1"
    AND
    prop.Name = "Property1"
    )

    , Property2 as(
    SELECT
    proc.Id as ProcessId,
    prop.Name,
    rrp.Value
    FROM Subject s
    LEFT JOIN Process proc
    ON s.SubjectId = proc.SubjectId
    LEFT JOIN RelationshipProcessProperty rpp
    on proc.ProcessId = rpp.ProcessId
    LEFT JOIN Property prop
    on rpp.PropertyId = prop.PropertyId
    WHERE
    s.Name = "Subject1"
    AND
    proc.Name = "Process1"
    AND
    prop.Name = "Property2"
    )

    SELECT
    p1.Name,
    p1.Value,
    p2.Name,
    p2.Value
    FROM
    Property1 p1
    LEFT JOIN Property2 p2
    on p1.ProcessId = p2.ProcessId

You can use this method to get multiple properties for the same process.

In order to have specified properties for a specified process, you would need to create Process Type Table:

    "ProcessType"
    PK ProcessType
    Type

And this does mean that you will need to add a foreign key to your process table to link it to which type it is. You can then link your ProcessType table to your Property table with a relationship table that defines all of the available types.

    "EligibleProcessProperties"
    PK EligibleprocessPropertiesId
    FK ProcessType
    Fk Property

Then to find out all of the available properties to that process type, you would have a relatively simple query

    SELECT
    p.Name
    FROM
    ProcessType pt
    LEFT JOIN EligibleProcessProperties epp
    on pt.ProcessTypeId = epp.ProcessTypeId
    LEFT JOIN Property p
    on epp.PropertyId = p.PropertyId
    WHERE
    pt.Type = "Type1"

I think that this is the sort of thing you're looking for (though i could be completely off). If this is what you're looking for, there's a really good post here that makes some good points.

Also, I'm almost 100% there are better ways to do my long ';with' query - but this is all I know. Hopefully someone else can provide a better one. The point is that with this design, you will need sub-queries one way or another.

Answer 2

If you already have an idea, what you want to store in the schema, a good strategy for creating the schema itself is:

1. Draw your requirements as ER-diagram ( https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model )
2. Translate the diagram into a SQL schema
3. If necessary, bring into 3rd normal form ( http://en.wikipedia.org/wiki/Database_normalization )

In your case, in step 1 you should have something like

 ______       ___________          _______     _____    ____________
| Subj. |____/ associated \_______| Proc. |___/ has \__| Proc.prop. |
|_______|    \____________/       |_______|   \_____/  |____________|

If you have different types of processes, you can think about specialization of the Process-entity. If you really want to chose the concrete process-properties for every process, you should probably make the has-relationship of cardinality m:n.

In the ER-diagram the validity check (your 3rd item) cannot be done even if you had another relationship. In an SQL schema you could use check constraints to enforce this, or let the application handle validity before inserting new processes.