public class Table1
{
public int Id {get;set;}
public string SomeField {get;set;}
public int? TAId {get;set;}
public TableA TA {get;set;}
public int? TBId {get;set;}
public TableA TB {get;set;}
//Repeat for about 100 foreign Keys
}
class represents and entity framaework class, at current there is a many to 1 relationship with a lot of tables and table1 (more than 100 and likely to grow)
or i can change it to a many to many setup which would remove all the foreign keys but I would have to build all the navigation tables and everytime I do a call it would be to the navigation table then to table1
I am wanting to know which is better, and is the number of foreign keys ever going to become an issue?
For now my understanding is that for some reason you are trying to centralize all foreign keys found in your database in a single table (?!). It seems it would cause more harm than good.
Which problem do you have with the standard approach ? You need an extra table only for many to many relations (and you should have much more 0 to n relations).
its just a normal table which tolds common information, then its linked by a foriegn key to all the tables which use it, but some tables use it multiple times and the number of foreign keys is growing, and will grow more when I had more parts to the site.
There isn't a problem as such I am just worried about the number of foreign keys in this table possibly causing an problem, no idea why it would, just interest if there is.
100 foreign keys seems large to me, but doing a bit od research i guess i am worrying about nothing.
its just a normal table which tolds common information, then its linked by a foriegn key to all the tables which use it, but some tables use it multiple times and the number of foreign keys is growing, and will grow more when I had more parts to the site.
There isn't a problem as such I am just worried about the number of foreign keys in this table possibly causing an problem, no idea why it would, just interest if there is.
100 foreign keys seems large to me, but doing a bit od research i guess i am worrying about nothing.
The original question infers that new columns must be added to a table as the application changes. This generally indicates the table is not properly normalized.
I don't understand the foreign key relationship for each new column though. It seems to me that would cause all kinds of downstream complexity.
Can you explain the problem you are trying to solve with this design?
Which kind of "common information" in plain English ?
It seems weird to have a database table being linked to more than 100 other tables. Also at some point it seems that you are telling that for each row only few (maybe even one ?) of those FKs are really not NULL ???
As pointed by mgebhard it seems you should just forget about your current design and explain us what is your goal ?
For now it seems a kind of "audit table" where you save something for each change and so you need to link back each row to one source table among 100+ tables ??? Still if something of this kind we would need to understand which information you try to track
(something like "recording who changed each row and when for all tables") before one can suggest an alternate design.
the situation is just a text table which has a text field and culturecode, the problem is text is used alot in certain areas, instead of have a navigation table which links 1 table with another, i have it linking 1 table with many others.
This has allowed me to keep the inheritence I was losing because many to manys are not built into entity framework core at this time
Participant
1677 Points
2543 Posts
a quick table question
Mar 31, 2019 08:24 PM|EnenDaveyBoy|LINK
Hi
I have a table in which has two main fields, then it has over 100 nullable foriegn keys which will only link to 1 other table at a time.
Is this ok?
I could turn it into lots of many to many's, which would sort of make it cleaner, but then I would have two joins.
Any thoughts?
All-Star
40241 Points
16771 Posts
Re: a quick table question
Mar 31, 2019 08:48 PM|mgebhard|LINK
Your question is unclear. Can you provide the example code?
Participant
1677 Points
2543 Posts
Re: a quick table question
Apr 01, 2019 02:11 AM|EnenDaveyBoy|LINK
public class Table1 { public int Id {get;set;} public string SomeField {get;set;} public int? TAId {get;set;} public TableA TA {get;set;} public int? TBId {get;set;} public TableA TB {get;set;} //Repeat for about 100 foreign Keys }class represents and entity framaework class, at current there is a many to 1 relationship with a lot of tables and table1 (more than 100 and likely to grow)
or i can change it to a many to many setup which would remove all the foreign keys but I would have to build all the navigation tables and everytime I do a call it would be to the navigation table then to table1
I am wanting to know which is better, and is the number of foreign keys ever going to become an issue?
All-Star
40241 Points
16771 Posts
Re: a quick table question
Apr 01, 2019 10:42 AM|mgebhard|LINK
I still do not understand the problem you are trying to solve. Having a variable number of columns usually indicates a design issue.
All-Star
44540 Points
15827 Posts
Re: a quick table question
Apr 01, 2019 11:02 AM|PatriceSc|LINK
Hi,
For now my understanding is that for some reason you are trying to centralize all foreign keys found in your database in a single table (?!). It seems it would cause more harm than good.
Which problem do you have with the standard approach ? You need an extra table only for many to many relations (and you should have much more 0 to n relations).
Participant
1677 Points
2543 Posts
Re: a quick table question
Apr 05, 2019 01:29 PM|EnenDaveyBoy|LINK
its just a normal table which tolds common information, then its linked by a foriegn key to all the tables which use it, but some tables use it multiple times and the number of foreign keys is growing, and will grow more when I had more parts to the site.
There isn't a problem as such I am just worried about the number of foreign keys in this table possibly causing an problem, no idea why it would, just interest if there is.
100 foreign keys seems large to me, but doing a bit od research i guess i am worrying about nothing.
All-Star
40241 Points
16771 Posts
Re: a quick table question
Apr 05, 2019 02:33 PM|mgebhard|LINK
The original question infers that new columns must be added to a table as the application changes. This generally indicates the table is not properly normalized.
I don't understand the foreign key relationship for each new column though. It seems to me that would cause all kinds of downstream complexity.
Can you explain the problem you are trying to solve with this design?
All-Star
44540 Points
15827 Posts
Re: a quick table question
Apr 05, 2019 02:49 PM|PatriceSc|LINK
Which kind of "common information" in plain English ?
It seems weird to have a database table being linked to more than 100 other tables. Also at some point it seems that you are telling that for each row only few (maybe even one ?) of those FKs are really not NULL ???
As pointed by mgebhard it seems you should just forget about your current design and explain us what is your goal ?
For now it seems a kind of "audit table" where you save something for each change and so you need to link back each row to one source table among 100+ tables ??? Still if something of this kind we would need to understand which information you try to track (something like "recording who changed each row and when for all tables") before one can suggest an alternate design.
Participant
1677 Points
2543 Posts
Re: a quick table question
Apr 05, 2019 02:58 PM|EnenDaveyBoy|LINK
sorted it, i have change my structure about which is actually better than I thought it would.
All-Star
40241 Points
16771 Posts
Re: a quick table question
Apr 05, 2019 03:36 PM|mgebhard|LINK
Can you explain how you changed the structure? I'm still trying to figure out the problem.
Participant
1677 Points
2543 Posts
Re: a quick table question
Apr 05, 2019 06:56 PM|EnenDaveyBoy|LINK
the situation is just a text table which has a text field and culturecode, the problem is text is used alot in certain areas, instead of have a navigation table which links 1 table with another, i have it linking 1 table with many others.
This has allowed me to keep the inheritence I was losing because many to manys are not built into entity framework core at this time