Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Database Design Question

 
Mike London
Ranch Hand
Posts: 1290
6
  • Mark post as helpful
  • send pies
  • 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
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 35977
422
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 1290
6
  • Mark post as helpful
  • send pies
  • 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
 
Dave Tolls
Ranch Hand
Posts: 2369
25
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 1290
6
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 2369
25
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 1290
6
  • Mark post as helpful
  • send pies
  • 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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!