- systems : each system has a field which describes the systems strength
- implants : describe an implant
- restrictions : each implant has zero or more restrictions
In most cases a restriction applies to all system strengths, but sometimes, a restriction can be dependant on system strength.
Later, I would like to be able to filter all restrictions that apply on a given system for one or more implants
So I would like to be able to specify for a restriction wether it is applicable for a certain system strength, or not, an this for all possible values of system strength.
How can I design my tables to acomplish this?
PS: how can I generate a create tables (DDL) statements in access? If I knew, I would have attached it to my question...
you don't need to generate a create tables (DDL) statements .
just create the table in table design or a make table query.
the 3 tables you showed should work.
and I guess tImplants and tRestrictions would be a sub tables of tSystems?
tSystem.SysID would exist in the sub tables:
tImplants.SysID and tRestricitons.SysID
The you would run a query to pull all systems having Restritions listed in tRestrictions table.
Thanks for your answer. Actually, the table for the systems is completely independent. I took a screenshot of the table relations.
An implant can be allowed to be scanned on a certain strength, but depending on this strength, restrictions might apply. One restriction can apply to more than one system strength.
So, the system strength, is important to filter implants and restrictions, but I did not define a direct relation from the system table to the other tables (see screenshot).
The question is: should I define a relation? And what should this relation look like? If I create a separate table with system strengths, there are different relations to different tables:
- for systems : every system should have one strength
- for implants : eligibility should be indicated for all strengths (for the moment, there are only two possible strengths, this is not going to change soon...)
- for restrictions : they generally apply to all strengths, but not always. So I should be able to 'deselect' strengths for which the restriction does not apply.
Maybe with the screenshot, all above make a little bit more sense (hopefully)...