Results 1 to 7 of 7
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Asking about data modelling

    Hi Guys,



    model is like here but i have doubts if it is created correctly:

    Click image for larger version. 

Name:	Przechwytywanie.JPG 
Views:	15 
Size:	55.1 KB 
ID:	33075

    Summary:

    1. Each client can have only specific records seen from all tables.
    Example:
    Client 1 has to see only 2 records from Versions table (so only 2 versions are suitable for him), but Client 3 should see 5 of them.

    2. Each Topology has different records within all tables.
    Example:
    Topology 1 is supporting Windows and Ubuntu but Topology 2 is supporting only Linux and Ubuntu.

    I am thinking all the time if it is a good design.

    Let's imagine situation like this:
    For topology1 there will be 6 record which matched with ServerSizeInfo table.
    And for the same Topology will be only 2 supported systems.

    So in Junction table we will have situation like this:

    Click image for larger version. 

Name:	Przechwytywanie.jpg 
Views:	14 
Size:	60.4 KB 
ID:	33076

    there is a lot of repeating in SupportedSystems_ID_FK.

    Because for each Topology i can have multiple serversizes and SupportedSystemsID.

    It is a way to optimize it ?

    Please help,
    Best Wishes,
    Jacek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Guys,

    i changed relationships:

    Click image for larger version. 

Name:	rel.jpg 
Views:	11 
Size:	54.8 KB 
ID:	33077

    Now it seems that should be good.

    One problem still. When i am creating query with all data like:
    Code:
    SELECT DISTINCT t_Versions.Version_name, t_Topologies.Topology_name, t_Clients.Client_info, t_Clients.Client_name, t_ServerSizeInfo.Memory_sizeLabels, t_SupportedSystems.Systems_name
    FROM t_Versions INNER JOIN (t_Topologies INNER JOIN (t_SupportedSystems INNER JOIN (t_ServerSizeInfo INNER JOIN t_Clients ON t_ServerSizeInfo.ServerSizeInfo_Id = t_Clients.ServerDetailedInfo_ID_FK) ON t_SupportedSystems.SupportedSystems_ID = t_Clients.SupportedSystems_ID_FK) ON t_Topologies.Topologies_ID = t_Clients.Topologies_ID_FK) ON t_Versions.Versions_ID = t_Clients.Version_ID_DK;
    Even there is distinct clause still i have duplicates in my output.
    When i am deleting Primary key (this is a cause of duplicating) duplicates are dissaperead.

    How can i avoid this?
    Best,
    Jacek

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Screenshot missing in last post
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Here you are!
    Attached Thumbnails Attached Thumbnails rel.jpg  

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks. Picture now visible in both threads
    When you populated your junction table, did you use code to prevent duplicate entries with different autonumber PK?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi rid,

    thank you.
    i was thinking rather to use cartasian join as you explained in other topic to input data in my junction table.

    Jacek

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ok but build in ways to prevent duplicates when you do so.
    E.g repetition of existing values or running a Cartesian join more than once
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-15-2018, 12:23 PM
  2. Replies: 7
    Last Post: 07-24-2017, 11:47 PM
  3. Replies: 9
    Last Post: 03-01-2017, 10:00 AM
  4. Replies: 1
    Last Post: 12-21-2011, 02:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums