Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Unanswered: Database design question

    I am developing a db, where I have a few objects:

    - 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?

    thanks,
    Yves

    PS: how can I generate a create tables (DDL) statements in access? If I knew, I would have attached it to my question...

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    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.
    tSystems
    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.

  3. #3
    Join Date
    Jul 2016
    Posts
    2

    Relations between tables

    Hi Ranman,

    Thanks for your answer. Actually, the table for the systems is completely independent. I took a screenshot of the table relations.

    Click image for larger version. 

Name:	relations.png 
Views:	7 
Size:	25.5 KB 
ID:	16985

    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)...

    Yves

  4. #4
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hi yfierens


    To model a database, it's better to

    1. Use a method like MERISE
    2. Use a tool (free) like JMERISE
    3. Begin with the conceptual data model as below


    Click image for larger version. 

Name:	systemStrength.png 
Views:	5 
Size:	34.0 KB 
ID:	17047

    But I didn't really understand relations between

    1. system
    2. strength
    3. restriction


    Could you give me cardinalities between us?


    The physical data model automatically generated with the tool is as follows:
    Click image for larger version. 

Name:	systemStrength_pdm.png 
Views:	4 
Size:	35.4 KB 
ID:	17048
    Last edited by informer; 07-14-16 at 14:45.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •