Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Question TABLE RELATIONS (m:n) BY HIGHER NUMBER OF TABLES ******************‏

    Hallo,

    I'm working on Project Management System,
    which I need to design and implement.

    There are more ways to do it and I'm now in design phase,
    so I will to design DB as good as possible.
    Later re-design / structural changes can be to expensive.

    Task:
    I need to manage Projects with all it's references.

    I need to connect Projects with other Objects such
    - other Projects or Subprojects
    - Articles (Products)
    - Programs
    - Documents (Docs - files, URLs)
    - ...

    Basic structure can look like:

    Code:
    Project
     |
     +- Child_project (or other Project, type = Project)
     +- Article
     +- Program
     +- Doc

    Table Relations:

    Code:
    Projects <-m---n-> Projects (Project can contain/refer to Child_projects/or link to other projects. Both are equal type as Project)
    Projects <-m---n-> Articles (Project can contain/refer to Articles, which can contain Programs, Docs)
    Projects <-m---n-> Programs (Project can contain/refer to Programs directly, which can contain Docs)
    Projects <-m---n-> Docs     (Project can contain/refer to Docs directly)
     
    Articles <-m---n-> Articles (Article can contain/refer to other Articles, for Ex. Car contains wheels, engine,... )
    Articles <-m---n-> Programs (Article can contain/refer to Programs, which can contain Docs)
    Articles <-m---n-> Docs     (Article can contain/refer to Docs)
     
    Programs <-m---n-> Docs     (Program can contain/refer to Docs)
    Docs     <-m---n-> Docs     (Doc     can contain/refer to other Docs)
    Complex structure can look like this:

    Code:
    Project
     |
     +- Child_project   (type = Project)
     |   |
     |   +- Article     (linked with Child_project)
     |   |   |
     |   |   +- Program (linked with Article)
     |   |   +- Doc     (linked with Article)
     |   |
     |   +- Program     (linked with Child_project)
     |   |   |
     |   |   +- Doc     (linked with Program)
     |   |
     |   +- Doc         (linked with Child_project)
     |       |
     |       +- Doc     (linked with Doc)
     |
     +- Articles
     |   |
     |   +- Program     (linked with Article)
     |   +- Doc         (linked with Article)
     |
     +- Programs
     |   |
     |   +- Doc         (linked with Program)
     |
     +- Doc
         +- Doc         (linked with Program)
    There are 2 ways how can I define Table relations:
    1.) Not normalized = 1 association table for all relations
    2.) Normalized = for each 2 tables relation in 1 association table

    1.) Not Normalized
    One Association Table for all relations !!!
    TableAssoc:
    - ID
    - Table1_Name
    - Table1_ID
    - Table2_Name
    - Table2_ID

    Example:

    Code:
    ID| T1Name   | T1ID| T2Name   | T2ID
    --+----------+-----+----------+-----
    1 | Projects |  10 | Articles | 100
    1 | Projects |  10 | Programs | 200
    1 | Projects |  10 | Docs     | 300
    1 | Articles | 100 | Programs | 220
    1 | Articles | 100 | Docs     | 320
    1 | Programs | 200 | Docs     | 330
    1 | Docs     | 300 | Docs     | 340
    1 | Docs     | 300 | Docs     | 340
    2.) Normalized
    8 Association Tables !!!:
    (by more Tables even more!!!)
    Code:
    Projects_x_Projects
    Projects_x_Articles
    Projects_x_Programs
    Projects_x_Docs
     
    Articles_x_Articles
    Articles_x_Programs
    Articles_x_Docs
     
    Programs_x_Docs
    Docs_x_Docs
    In future i can have 20 or more Tables, that i want to connect!
    Number of Assoc Tables = Factorial(20-1) = 1,2 x 10 exp.on 17 !!!


    What would you me to recomend?
    How can I store table relations (m:n) for my Project Management?


    Thank you very much for any response!

    Rudi

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    One possibility is to give each Article, Program, Doc, etc a unique AssetID. Then create one new table:

    AssociatedAssets {AssetID, AssociatedAssetID, TypeOfAssociation} KEY {AssetID, AssociatedAssetID}

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Rudi

    1 In Relational databases, if you Normalise, the result is more tables. This is ordinary and normal, nothing to be afraid of. The more tables will have (a) fewer columns (b) fewer indices (c) fewer rows, and therefore (simple physics) provide substantially better performance, than unnormalised tables.

    2 When you add tables and columns (or relations as associative tables) to a Normalisd database, you simply add; you do not have to change the existing structure. In unnormalised files, you have to change the structure, or worse, duplicate the new columns in more than one place.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Nov 2009
    Posts
    3
    Quote Originally Posted by Derek Asirvadem View Post
    Rudi
    ...
    2 When you add tables and columns (or relations as associative tables) to a Normalisd database, you simply add;
    ...
    Thanx, but...
    In future i can have 20 or more Tables, that i want to connect!
    Number of Assoc Tables = Factorial(20-1) = 1,2 x 10 exp.on 17 !!!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Then it looks like you should follow dportas's solution in this case. It might of been worth mentioning the number of types of objects that you were planning on storing as this does influence suggested designs - I guess 2 million trillion tables might be a bit much to look after in this case. Though there is a guy on the MySQL forum at the moment with 100 thousand tables in his database.

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by thrall_rudo View Post
    Thanx, but...
    In future i can have 20 or more Tables, that i want to connect!
    Number of Assoc Tables = Factorial(20-1) = 1,2 x 10 exp.on 17 !!!
    Willickers! That's a lot of tables.

    The fact that you've considering n! of anything should suggest that your initial design is flawed.

    In real life, everything isn't directly related to everything else.

    You also can't possibly write code that can account for everything being related to everything else.

    Try to imagine a user looking at this thing. You say:

    Projects <-m---n-> Projects (Project can contain/refer to Child_projects/or link to other projects. Both are equal type as Project)
    Projects <-m---n-> Articles (Project can contain/refer to Articles, which can contain Programs, Docs)
    Projects <-m---n-> Programs (Project can contain/refer to Programs directly, which can contain Docs)
    Projects <-m---n-> Docs (Project can contain/refer to Docs directly)

    So, the window for the project will allow any arbitrary collection of stuff? Why does it both contain *and* refer to all these things?

    Ultimately, what is a project then?

    What does it even mean for a project to have a "child" project? Specifically, what does this make immediately obvious to your user?

    What if your hierarchy looked more like this:

    Code:
    Project has DependentProjects (( These are other projects that depend on this project being completed. ))
    Project contains Files
    Doc ISA File
    Article ISA File
    Program ISA File
    ...
    You now have a realistic set of object types that:

    a. are not going to lead to factorial expansion

    b. probably will make sense to users

    Note that containing and ISA relationships are different things. Doc, Article, and Program are not contained by or referred to by File. Rather, they just happen to share some common attributes.

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by mike_bike_kite View Post
    I guess 2 million trillion tables might be a bit much to look after in this case.
    Only if you're weak.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by sco08y View Post
    In real life, everything isn't directly related to everything else.
    Actually I once produced a configuration management system for a bank where pretty much every type of item we stored WAS related in some fashion or to the other types.
    • Users
    • Departments
    • PCs
    • Servers
    • Software
    • Licenses
    • Projects
    • Suppliers
    • Networks
    • Support teams
    • Buildings
    • ...

    The list went on and on - I think we had about 40 types of item in the database. The aim was to move all the existing but separate configuration management systems that dealt with each "small world" into one single system that could deal with everything, provide better reporting and be cheaper to maintain all at the same time. I guess we could of built separate tables for each new relationship but this would of been a full time job in itself.

  9. #9
    Join Date
    Nov 2009
    Posts
    3
    Quote Originally Posted by sco08y View Post
    Willickers! That's a lot of tables.

    The fact that you've considering n! of anything should suggest that your initial design is flawed.

    In real life, everything isn't directly related to everything else.

    You also can't possibly write code that can account for everything being related to everything else.

    Try to imagine a user looking at this thing. You say:

    Projects <-m---n-> Projects (Project can contain/refer to Child_projects/or link to other projects. Both are equal type as Project)
    Projects <-m---n-> Articles (Project can contain/refer to Articles, which can contain Programs, Docs)
    Projects <-m---n-> Programs (Project can contain/refer to Programs directly, which can contain Docs)
    Projects <-m---n-> Docs (Project can contain/refer to Docs directly)

    So, the window for the project will allow any arbitrary collection of stuff? Why does it both contain *and* refer to all these things?

    Ultimately, what is a project then?

    What does it even mean for a project to have a "child" project? Specifically, what does this make immediately obvious to your user?
    Sorry,
    maybe I used bad Designation for my System.
    It's not only Project management system.
    It should be project management system on the first place,
    but it should also be:
    - CMS = content management system
    - ERP system
    - CRM system

    Data Objects:

    1.) View from the side: Projects

    1.1.) Project-Project
    The project in root level can be project (or Order etc...),
    which can consists of sub-projects/sub-tasks:
    Example:
    Project/Order: Production of car Audi S4 3.0T
    Subprojects:
    - "Production of doors"
    - "Production of wheels"
    - "Production of chassis" ...

    1.2) Project-Article
    Articles/Products which will be used/produced in Production operations
    - product door
    - product wheel
    - product chassis

    1.3) Project-Document
    Project related documents:
    - Image Scan copy of order
    - E-Mails
    - Text documents
    etc.

    ....

    2.) Other view from side Article:

    This view shows all Projects/Orders where was produced Audi S4
    ...

    3.) Documents View:

    Show me all Projects where I use EULA document for small companies etc...

    ...

    I'm now testing de-normalized solution:
    1 Assoc table for all relations:
    - ID (int)
    - ParentTableNr (int)
    - ParentTableRowID (int)
    - ChildTableNr (int)
    - ChildTableRowID (int)

    As you can see I will work only with integers so big association table with associations for all tables in projects will not be a problem.

    I'll see if it's a good way...
    Handling till now is OK.

    Thanx to all for replies...
    Last edited by thrall_rudo; 11-16-09 at 19:06.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My advice is to trake a step back

    Collect and document all unique elements of data (not the values of data, but the types)

    Don't worry about where they fall right now

    Make sure each element has a sound business description for each. This is the first part of a data dictionary.

    Then you can set about putting each attribute into like containers (Entities)

    If you have a modeling tool (ErWin, Visio Pro) you can start to create a Logical ER Diagram. In a Logical model, you don't have to worry about physical attributes.

    Take this Logical model and review it with customers, business partners, and anyone else who would want to listen

    Take notes and adjust your model

    Even before all of this, you could actually make a business flow diagram...this would help with the collection of attributes

    Good Modeling, documentation, business flow, data flow processes could take months if done correctly
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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
  •