locked
a quick table question RRS feed

  • Question

  • User1034446946 posted

    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?

    Sunday, March 31, 2019 8:24 PM

Answers

  • User1034446946 posted

    sorted it, i have change my structure about which is actually better than I thought it would.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 5, 2019 2:58 PM

All replies

  • User475983607 posted

    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?

    Your question is unclear.  Can you provide the example code?

    Sunday, March 31, 2019 8:48 PM
  • User1034446946 posted
    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?

    Monday, April 1, 2019 2:11 AM
  • User475983607 posted

    I still do not understand the problem you are trying to solve.  Having a variable number of columns usually indicates a design issue.  

    Monday, April 1, 2019 10:42 AM
  • User753101303 posted

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

    Monday, April 1, 2019 11:02 AM
  • User1034446946 posted

    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.

    Friday, April 5, 2019 1:29 PM
  • User475983607 posted

    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?  

    Friday, April 5, 2019 2:33 PM
  • User753101303 posted

    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.

    Friday, April 5, 2019 2:49 PM
  • User1034446946 posted

    sorted it, i have change my structure about which is actually better than I thought it would.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 5, 2019 2:58 PM
  • User475983607 posted

    sorted it, i have change my structure about which is actually better than I thought it would.

    Can you explain how you changed the structure?  I'm still trying to figure out the problem.

    Friday, April 5, 2019 3:36 PM
  • User1034446946 posted

    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

    Friday, April 5, 2019 6:56 PM