The general idea is that you create a subform that's bound to your link table, and make one of the fields in the subform a combo box bound to the child record ID that's in your link table. Fill the combo box using records in the child table.
Then you handle the NotInList event on the combo box to prompt the user to add the record to the child table (or whatever is appropriate for your application).
NotInList Event
Then if you want to actually remove rows from the child table when they are no longer referenced by the link table, handle the appropriate deletion event on your subform, and check for unreferenced records.
I've implemented something like this once, but I think it was in Access 2000 on a machine I don't have with me at the moment. I'll try to check it later out of curiosity.
Option 1
*This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature…
It does not necessarily complicate query SQL (see conclusion below).
…and doesn't readily scale to more conditions…
It scales readily to more conditions, as long as there are still a fixed number of conditions, and there aren't dozens or hundreds.
However, it does enforce the requirement of a certain number of conditions per (X,Y) pair. In fact, it is the only option here that does so.*
It does, and although you say in a comment that this is "the least important of my requirements", you haven't said it doesn't matter at all.
Option 2
One downside to this is that it doesn't specify the number of conditions for each pair. Another is that when I am only considering the initial relationship…I then have to add a DISTINCT clause to avoid duplicate entries…
I think you can dismiss this option because of the complications you mention. The objx_objy
table is likely to be the driving table for some of your queries (eg "select all properties applied to a feature", which I am taking to mean all properties applied to an objx
or objy
). You can use a view to pre-apply the DISTINCT
so it is not a matter of complicating queries, but that's going to scale very badly performance-wise for very little gain.
Option 3
Does it make sense though to create a new ID that identifies nothing other than existing IDs?
No, it doesn't — Option 4 is better in every regard.
Option 4
…it basically duplicates an entire table multiple times (or feels that way, anyway) so also doesn't seem ideal.
This option is just fine — it is the obvious way of setting up the relations if the number of properties is variable or subject to change
Conclusion
My preference would be option 1 if the number of properties per objx_objy
is likely to be stable, and if you can't imagine ever adding more than a handful extra. It is also the only option that enforces the 'number of properties = 3' constraint — enforcing a similar constraint on option 4 would likely involve adding c1_p_id
… columns to the xy table anyway*.
If you really don't care much about that condition, and you also have reason to doubt that the number of properties condition is going to be stable then choose option 4.
If you aren't sure which, choose option 1 — it is simpler and that is definitely better if you have the option, as others have said. If you are put off option 1 "…because it complicates the SQL to select all properties applied to a feature…" I suggest the creating a view to provide the same data as the extra table in option 4:
option 1 tables:
create table prop(id integer primary key);
create table objx(id integer primary key);
create table objy(id integer primary key);
create table objx_objy(
x_id integer references objx
, y_id integer references objy
, c1_p_id integer not null references prop
, c2_p_id integer not null references prop
, c3_p_id integer not null references prop
, primary key (x_id, y_id)
);
insert into prop(id) select generate_series(90,99);
insert into objx(id) select generate_series(10,12);
insert into objy(id) select generate_series(20,22);
insert into objx_objy(x_id,y_id,c1_p_id,c2_p_id,c3_p_id)
select objx.id, objy.id, 90, 91, 90+floor(random()*10)
from objx cross join objy;
view to 'emulate' option 4:
create view objx_objy_prop as
select x_id
, y_id
, unnest(array[1,2,3]) c_id
, unnest(array[c1_p_id,c2_p_id,c3_p_id]) p_id
from objx_objy;
"select all properties applied to a feature":
select distinct p_id from objx_objy_prop where x_id=10 order by p_id;
/*
|p_id|
|---:|
| 90|
| 91|
| 97|
| 98|
*/
dbfiddle here
Best Answer
Since Template must be related to a Region, you need a foreign key from
Template.region_id
toRegion.id
Template.region_id
will be NOT NULLable.Template.country_id
will be NULLable.Make a unique constraint on the composite key
(Country.id, Country.region_id)
Then you can create a foreign key from
(Template.country_id , Template.region_id)
to(Country.id, Country.region_id)
This ensures that if the Template is related to a Country, then the
Template.region_id
is the same as the relatedCountry.region_id
Luckily (for you) when a row's composite foreign key contains one or more null values, then the row is "valid" regardless of the existence of rows in the referenced table. So having a NULL
Template.country_id
and a NOT NULLTemplate.region_id
will be fine.One interesting side effect of this "doubly constrained" system is that it is difficult to update the
region_id
column of aCountry
. Any attempt to updateCountry.region_id
will cause a constraint violation if there are any Templates that contain references to that Country, since the(Template.country_id , Template.region_id)
pair will not refer to an existing(Country.id, Country.region_id)
.If you must allow this type of update, then you can use an
ON UPDATE CASCADE
clause when creating the(Template.country_id , Template.region_id)
foreign key.