Results 1 to 11 of 11
  1. #1
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10

    Hierarchical database design

    I'm a CAD programmer so forgive the possible idiocy of this question.

    I have a database which is used to create 3D mechanical designs.



    Every product and part has an 8 digit code, the SAP code.

    Currently I have a "central" two column table which consists of the SAP code and an optional description, CodiceSAP.

    Then the sub-tables have more details about each part, so Tubi (tubes) have materials diameters etc, and EndPlates have details about end plates, and so on etc. Tubi has its own column linked back to CodiceSAP, and so does EndPlates, and all the other "sub part" tables.

    Here it is:

    Click image for larger version. 

Name:	Central_ode.png 
Views:	28 
Size:	68.0 KB 
ID:	45746

    Currently I'm populating the database by hand and it is a bit of a bind to have to fill in the central CodiceSAP table before having to fill in, for example, the Tubi table.

    My question is: Is that central CodiceSAP table neccessary?

    I can see that it helps me prevent having an end plate with the same SAP code as a tube, especially if I put a description there, but is that enough of a reason to have that central table? Am I missing some subtle advantage...?

    TIA!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    might be easier if you have one table for all parts. OK there will be some redundant fields depending on the part, but you can make some fields multi functional in that for one part it might represent height and in another diameter. Either way, empty fields take up no space.

    Your diagram is showing a lot of 1 to 1 relationships which is a very rare requirement - and I suspect not relevant in this case. Looks like you can combine EndPlates, GruppoColl, cctAssemblies, CodiciSAP, Tubi and GruppoDistrib into one table

    You also appear to have a number of relationship loops which also is not a normal requirement

    And finally, use forms and subforms for data input, with properly designed relationships the foreign key will be populated automatically for a new record.

  3. #3
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    Looks like you can combine EndPlates, GruppoColl, cctAssemblies, CodiciSAP, Tubi and GruppoDistrib into one table
    That looks like a terrible idea. Some tables have 20 columns, some have 3. And I think it breaks some of the relational database rules.

    And sharing different sorts of data in the same column also seems to be a terrible and confusing idea.

    IMHO

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Difficult to say without understanding exactly what the db is supposed to support. Doesn't help me that I can't read the names - not saying that's something you should rectify, just saying my suggestions are somewhat hampered by those factors. The first you could 'fix' the latter you are not expected to fix. So -

    perhaps what you should have is your 2 field table (I interpret that as a SAP identifier and a field that denotes what part that is for. If that isn't "Tubie" or "endplates" then I don't know)
    then a table of materials used in every part
    then one table for parts
    then junction table for materials to parts (assuming material is used in more than one part). Not sure what else due to lack of understanding the process.

    With properly designed form(s) you should be able to select (combo) SAP#, get the part info and show a list for all the materials used in that part. I'm not seeing a way to connect the drawing number related to the part (I'm assuming a part is a given thing, like a product, and not an instance or reproduction of a part. That would be a "job" or "order" to my way of thinking).

    Your design won't pass the 'acid' test for normalization because you're creating a table for each iteration of an entity (part). Say you have a query that relates SAP to a part. You need x queries for x parts, or if you add a table for a new part and there is a basic query that joins all your current tables, then you must add this new part table and modify the query design, as well as any forms and reports that rely on it. That is an acid test for normalization. All parts should be in one table. If you need to relate attributes of parts that are not universal (e.g. something square has no diameter) that's where junction tables come in.

    EDIT - forgot to say that in addition to comments about 1 to 1 relationships it is usually a sign of improper design where a table has nothing but primary keys.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Some tables have 20 columns, some have 3. And I think it breaks some of the relational database rules.
    actually, it doesn't. the relationship is between one entity and another. Doesn't matter what defines that entity. On your basis, you add a new part type, you have to create a new table (which breaks referential integrity rules), new relationships and no doubt more changes to your app.

    And sharing different sorts of data in the same column also seems to be a terrible and confusing idea.
    it was just a suggestion, if you do it right it is not a problem - so long as the data is of the same type. I've written many apps for production costing, one product, different processes, each process having different parameters. Simplistically, just two or three tables and the rules clearly defined as to what to do for any situation. But it's up to you, it's your app.

  6. #6
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10
    Quote Originally Posted by Micron View Post
    Difficult to say without understanding exactly what the db is supposed to support. Doesn't help me that I can't read the names - not saying that's something you should rectify, just saying my suggestions are somewhat hampered by those factors. The first you could 'fix' the latter you are not expected to fix. So -

    perhaps what you should have is your 2 field table (I interpret that as a SAP identifier and a field that denotes what part that is for. If that isn't "Tubie" or "endplates" then I don't know)
    then a table of materials used in every part
    then one table for parts
    then junction table for materials to parts (assuming material is used in more than one part). Not sure what else due to lack of understanding the process.

    With properly designed form(s) you should be able to select (combo) SAP#, get the part info and show a list for all the materials used in that part. I'm not seeing a way to connect the drawing number related to the part (I'm assuming a part is a given thing, like a product, and not an instance or reproduction of a part. That would be a "job" or "order" to my way of thinking).

    Your design won't pass the 'acid' test...

    EDIT - forgot to say that in addition to comments about 1 to 1 relationships it is usually a sign of improper design where a table has nothing but primary keys.
    Thanks for that feedback. Actually I have a parts table, it is the central two column table.

    The other poster mentioned 1 to 1 problem too. I'll have to think about that.

  7. #7
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    actually, it doesn't. the relationship is between one entity and another. Doesn't matter what defines that entity. On your basis, you add a new part type, you have to create a new table (which breaks referential integrity rules), new relationships and no doubt more changes to your app.
    As I say I'm a CAD programmer so... why does adding a new table because a new sort of part been invented break the rules?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    why does adding a new table because a new sort of part been invented break the rules?
    The answer to that is in 4th paragraph of my previous post, but I welcome Ajax's input as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    Your diagram is showing a lot of 1 to 1 relationships which is a very rare requirement
    I was thinking about this, is the problem that: if I have a list of values for a given field, and that list is only used in one table, then the list is better put in the definition of that table? (Instead of another table)

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Pretty much. Difficult to advise without knowing what the app is required to do but on the basis this is framework for defining a single part with their potential parameters I would expect only one table with redundant fields dependant on requirements. You would have other tables for data such as supplier.

    One of the rules of dB design is to not build meaning into table names - which is an excel approach and what you have with batteries, tubes etc.

    May not be the best example but take a family of two parents and children. On your basis you might have 3 tables, husband, wife and children as each has differing parameters. On my basis you would have two tables, one for ‘people’ and one to define the relationship,

    Access is not excel. In excel, presentation and storage is all in the same ‘view’. With a database storage Is in tables and presentation in forms and reports via queries.

  11. #11
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    I would have only one table with redundant fields dependent on requirements.
    Hmm I'll have to think about all this.

    Quote Originally Posted by Ajax View Post
    Access is not Excel.
    I know, I have to maintain a custom electrical design program based on tables in Excel, written by someone else, and I want to cry every time I need to modify it.

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

Similar Threads

  1. Replies: 7
    Last Post: 07-30-2019, 02:31 PM
  2. Displaying Hierarchical Links
    By IGN_Mikey in forum Forms
    Replies: 2
    Last Post: 06-09-2015, 11:16 AM
  3. Replies: 2
    Last Post: 10-28-2014, 11:41 AM
  4. Hierarchical queries!!!
    By stoly in forum Access
    Replies: 10
    Last Post: 12-01-2010, 06:36 PM
  5. Displaying Hierarchical data
    By Gerry in forum Programming
    Replies: 10
    Last Post: 04-17-2010, 06:50 PM

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
  •  
Other Forums: Microsoft Office Forums