Relation with multiple table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Relation with multiple table

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2014-08-06 : 02:49:15

I have main table and other 12 tables .
i have to create relation table with relation type many to many
for each table of the 12 tables
what is the best design to solve that.
1- create one table (ID , MainID , TrxID , TableID) TrxID have no relation .
2- create 12 tables Contain (ID , MainID, TrxID) TrxID have relation with it's table.
3 - Create one table having 12 columns for relation for each table
(ID , MainID , TrxID_Table1,TrxID_Table2,TrxID_Table3,....ect) having 11 null values for each inserted row.
what is the best design to solve that.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-06 : 11:17:49

I'm afraid that I don't quite understand your question. Perhaps some specifics would bring the issue into focus. If I do understand you, there is a main table which has a many to many relationship with each of twelve other tables. My first response would be that you'd need a junction table for each of the twelve table relationships; one to connect Main with Table1, another to connect Main with Table2, etc.). I am assuming that each of the twelve tables are independent of each other but, again, I may simply be missing your issue entirely.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

samir.first
Starting Member

34 Posts

Posted - 2014-08-07 : 16:59:22

Thank you Bustaz Kool,
and I Will explain to you in detail
There are 12 screen for each screen her table
Table 1
ID Date ValueTable1
1001 12-07-2014 100

Table 2
ID Date ValueTable2
1255 14-07-2014 2000

Table 3
ID Date ValueTable3
1366 18-07-2014 500
...etc
And I have TableMain
TableMain
ID Date MainValue
210 12-07-2014 100
211 14-07-2014 2000
212 18-07-2014 500
what is the best design to reference Between MainTable And 12 table .
===================================================
First Case
Create 12 Table Relation
IDTable1 IDTableMain
1001 210
And
IDTable2 IDTableMain .... etc
1255 211

Or second Case
Create One Table
IDTable TableType IDTableMain
1001 1 210
1255 2 211
1366 3 212

Or Third Case

Create One Table
IDTableMain IDTable1 IDTable2 IDTable3 ... etc
210 1001 NULL NULL
211 NULL 1255 NULL
212 NULL NULL 1366

Please Help for best Design

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-07 : 17:29:05

I'm sorry but I simply don't understand what you are trying to accomplish here. I don't see any many-to-many relationships. What does the Main table represent? What do each of the twelve tables represent? I'd like to help but you have got to make it easy to be helped.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

MohamedAliSalim
Starting Member

9 Posts

Posted - 2014-08-09 : 04:18:16

Is this you want ?
What is the best design of your table ?
Or is there other best design??

[url]http://www.4shared.com/file/PJ5hGf9nba/design_1.html[/url]
[url]http://www.4shared.com/file/d7REgYsxce/design_2.html[/url]
[url]http://www.4shared.com/file/a1bAKRXUce/design_3.html[/url]

All in one
[url]http://www.4shared.com/file/V21LUNuXce/designs.html[/url]

Mohamed Ali Salim

samir.first
Starting Member

34 Posts

Posted - 2014-08-09 : 04:21:07

Thank you Eng Mohamed Ali
yes , this is what i want
Eng Bustaz Kool
please check Eng Mohamed Ali URLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources