• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database Design Question

 
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

Say you have two tables which will have foreign keys in a M:M resolver table.

For DB design, is it better to:

(1) Have a single "FK" Field and also a discriminator column to indicate for which table each foreign key points for that single FK field in the M:M resolver table?

OR

(2) Have two fields in the M:M resolver table (one field each for each of the two tables), knowing that in any one row of the M:M resolver table one of the two FK fields will always be blank?

A DB friend of mine recently told that SQL Server, for example, won't allocate space for empty fields so having two fields wouldn't "waste space" (since, as he put it, SQL Server does things at a "page level"). Not sure this is true. Is that true? If so, I'm wondering how many databases don't waste space with empty fields.

-----

I would tend to opt for choice (1) above and modeled it that way in my ERD, but wanted to see what might have changed in how DBs optimize blank fields.

Thanks for any opinions or advice...

- mike
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mike,
I'd go with option 1 as well. Not becuase of the "space" problem though; because it is clearer. As a % of your data, the amount of disk space being discussed here seems tiny.
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jeanne Boyarsky wrote:Mike,
I'd go with option 1 as well. Not becuase of the "space" problem though; because it is clearer. As a % of your data, the amount of disk space being discussed here seems tiny.



Sounds good, thank you!

Yes, the reason I opted for option (1) was design clarity, but I don't like to create extra fields that where one would always be blank.

Thanks for your reply.

- mike
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm a bit confused here...probably a Monday thing.
But could you show an example of what the two versions look like?

When I think of a Many-to-Many relationship I think:
TABLE_1 with an ID column plus others.
TABLE_2 with an ID column plus others.
MAP_TABLE_1_2 with FK on TABLE_1.ID and FK on TABLE_2.ID, and a PK on both those columns.

Or is there something else you are actually asking?
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I'm a bit confused here...probably a Monday thing.
But could you show an example of what the two versions look like?

When I think of a Many-to-Many relationship I think:
TABLE_1 with an ID column plus others.
TABLE_2 with an ID column plus others.
MAP_TABLE_1_2 with FK on TABLE_1.ID and FK on TABLE_2.ID, and a PK on both those columns.

Or is there something else you are actually asking?



Assume you have table 1 called "Table1" and another table called "Table2", with PKs TAB1_ID and TAB2_ID.

Now, in the M:M table, you could either have:

(1) a (single) MAP_TABLE_TABFK with another field (a discriminator to say which table the FK is for) or

(2) two separate FK fields in the MAP table, one for each FK.

I chose (1) so there wouldn't always be a blank field (one FK or the other) in each record and it's cleaner.

So you might have data, assuming (1)  like this in the MAP table:

MAP_ID     MAP_TAB_FK      MAP_DISC
1                 100                     T1
2                 100                     T2
3                 222                     T1


-----------------
Instead of (2):
-----------------
MAP_ID     MAP_TAB1_FK      MAP_TAB2_FK
1                 100                  
2                                             100
3                 222                  

----

Does that clarify what I was asking?

-- mike
---



 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nope, because with number 1 you have no foreign key.
You can't, because MAP_TAB_FK maps to one of two tables.
Indeed, how does that table allow for the many to many?

For the second one, why are any of those blank?
If 100 does not map to any row in table2 then there should be no row in the mapping table.

This is why I'm a bit confused, as neither of those represents a Many-to-Many mapping properly.
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Nope, because with number 1 you have no foreign key.
You can't, because MAP_TAB_FK maps to one of two tables.
Indeed, how does that table allow for the many to many?

For the second one, why are any of those blank?
If 100 does not map to any row in table2 then there should be no row in the mapping table.

This is why I'm a bit confused, as neither of those represents a Many-to-Many mapping properly.



In both cases, Tables T1 and T2 have "ID" fields which would be the FK in either the single field (with a discriminator) or in the second case with two fields.

Yes, I didn't include the M:M part of the diagram (the other linked table) as that wasn't the main question I was asking.

Thanks,

- mike
 
reply
    Bookmark Topic Watch Topic
  • New Topic