Since a Knowledge might be a Method, I'm wondering if it would make more sense to store them both in the same table:
KnowledgeAndMethods
--------------------
ID - unique primary key
Description
parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
knowledge_type
Step
Step_ID
Based on your example, I think the data would be stored as:
ID Description Parent_knowledge Step Step ID
-------------------------------------------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity" 1 "Step 1" S1
3 "Find unknown time" 2 "Step 2" S2
4 "Apply acceleration formula" 3 "Step 3" S3
5 "Land rocket ship"
With recursive queries, it could be very easy to get data back from this kind of table.
You could have a KnowledgeSubtype or MethodSubtype column if you want, but I'm not sure if you would gain anything.
Based on comments, it sounds like a many-to-many relationship is wanted. So here's another try at it:
KnowledgeAndMethods
--------------------
ID - unique primary key
Description
parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
knowledge_type
Methods_Steps
Parent_Knowledge_ID - the ID of the parent Knowledge
Step
Step_ID - The ID local within a set of Steps
Method_ID - The ID of the Method that is a Knowledge
So now the previous example might look like:
KnowledgeAndMethods:
ID Description
--------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"
3 "Find unknown time"
4 "Apply acceleration formula"
Methods_Steps:
Knowledge_ID Step Step_ID Method_ID
--------------------------------------------
1 "Step 1" 1 2
1 "Step 2" 2 3
1 "Step 3" 3 4
5 "Step 9" 6 2 <---- Here's an example of "Find Unknown Velocity" being a different "Step" in a diffent "Knowledge"
I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:
create table car_configuration (
config_id integer primary key,
car_id integer references cars (car_id)
);
create table car_config_parts (
config_id integer references car_configuration (config_id),
part_id integer references parts (part_id),
constraint car_config_part_pk primary key (config_id ,part_id)
);
create table car_config_tunings (
config_id integer references car_configuration (config_id),
tuning_id integer references tuning (tuning_id),
constraint car_config_tuning_pk primary key (config_id, tuning_id)
);
You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.
Best Answer
It sounds like you are describing a mutual relationship such that there is no difference between
(1,2)and(2,1). In this case you should design your code to always insert the lower userId as the first value and the higher as the second. Then you can use AlexKuznetsov's suggestion and add a CHECK constraint of(userId<relatedUserId). The code can then catch the duplicate exceptions and ignore them since the relationship already exists.Note: I flipped the comparison from > to < because the lower number being first makes more sense to me. It works either way and may make more sense the other way in your environment.