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

thrall_rudo · #1 (**permalink**) 11-11-09, 08:29

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

dportas · #2 (**permalink**) 11-11-09, 09:02

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}

Derek Asirvadem · #3 (**permalink**) 11-12-09, 03:10

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.

thrall_rudo · #4 (**permalink**) 11-14-09, 12:43

Quote:

Originally Posted by Derek Asirvadem

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

mike_bike_kite · #5 (**permalink**) 11-14-09, 14:11

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.

sco08y · #6 (**permalink**) 11-14-09, 22:41

Quote:

Originally Posted by thrall_rudo

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.

sco08y · #7 (**permalink**) 11-14-09, 22:42

Quote:

Originally Posted by mike_bike_kite

I guess 2 million trillion tables might be a bit much to look after in this case.

Only if you're weak.

mike_bike_kite · #8 (**permalink**) 11-15-09, 15:55

Quote:

Originally Posted by sco08y

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.

thrall_rudo · #9 (**permalink**) 11-16-09, 18:03

Quote:

Originally Posted by sco08y

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

Brett Kaiser · #10 (**permalink**) 11-17-09, 09:55

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

Internet Services

Web Development

Digital Marketing

Consumer Tech