SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Relation with multiple table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/06/2014 :  02:49:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 08/06/2014 :  11:17:49  Show Profile  Reply with Quote
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
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/07/2014 :  16:59:22  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 08/07/2014 :  17:29:05  Show Profile  Reply with Quote
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
Go to Top of Page

MohamedAliSalim
Starting Member

Egypt
5 Posts

Posted - 08/09/2014 :  04:18:16  Show Profile  Reply with Quote
Is this you want ?
What is the best design of your table ?
Or is there other best design??

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

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

Mohamed Ali Salim
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/09/2014 :  04:21:07  Show Profile  Reply with Quote
Thank you Eng Mohamed Ali
yes , this is what i want
Eng Bustaz Kool
please check Eng Mohamed Ali URLs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000