Bridging Table - To use or not?

[mds1256]

Just wondering what people's views are on bridging tables.

 

Should I use them rather than creating a new field in the table to reference the other tables row number.

 

 

e.g.

 

Bridging table method:

 

Users table:

userID

userName

userPassword

1

abcd

1234

 

 

Address table:

AddressID

houseNo

streetName

postCode

5

52

New Street

AA2 4DD

 

Bridge table:

bridgeID

userID

addressID

1

1

5

 

 

 

 

 

Same table method:

 

Users table:

userID

userName

userPassword

addressID

1

abcd

1234

5

 

 

Address table:

AddressID

houseNo

streetName

postCode

5

52

New Street

AA2 4DD

 

[fugix]

if you want a field from another table you can use the JOIN method...

[mikosiko]

@mds1256: The method to choose depend on the kind of relationship that you want to implement

 

in your example the bridge method will be useful if your user could have 0 or more addresses (Postal, Commercial, etc)

the "same table" option that you show imply that the user will have only 1 (or 0) address.

[mds1256]

@mds1256: The method to choose depend on the kind of relationship that you want to implement

 

in your example the bridge method will be useful if your user could have 0 or more addresses (Postal, Commercial, etc)

the "same table" option that you show imply that the user will have only 1 (or 0) address.

 

Very true!

 

but if you change the location of the matching id field onto the address table you could have multiple address entries with the same userID and it would give you the same result

 

So what is the benefit, I guess if you want a many to many relationship this will justify it

[ignace]

A user can have multiple addresses (home, work, ..). And multiple users can have the same address (colleagues, family members, students in the same house, ..). But you don't want to share addresses among users because when one edits his address, your packages may end up with the wrong customer. The relation therefor is:

 

User (user_id, ..)

Address (address_id, user_id, ..)

 

You don't need the bridge table because each address is unique for each customer even if multiple customers live on the same address.

[mikosiko]

Ignace gave you some points... but again, the relation that you implement is going to be determined by your business model.. sometimes you want/need to have multiples users (related of course) sharing the same address or addresses ... depend on your specific needs.

 

In the case that Ignace is explaining, if you have 2 related users sharing the same address(es) you will be duplicating information in your address table, therefore the table will be  no normalized (which could be an option too).

 

Example (as per your last post and apparently same Ignace idea):
Users
1 Jhon Doe
2 Mary Doe (spouse)

Address
id address        city     zip       addresstype           userid
1 kitty Road 10 Atlanta 23245     1(home)                   1
2 kitty Road 20 Atlanta 23248     2(comm)                   1
3 kitty Road 10 Atlanta 23245     1(home)                   2

 

instead of (using a bridge)

 

Example (as per your last post and apparently same Ignace idea):
Users
1 Jhon Doe
2 Mary Doe (spouse)

Address
id address        city     zip       
1 kitty Road 10 Atlanta 23245     
2 kitty Road 20 Atlanta 23248    
  

User-Address
id  userid  address-id   type
1     1              1             1
2     1              2             2
3     2              1             1

 

[ignace]

@mikosiko Sharing addresses is not an option in this case, since:

 

Assume I life on SesameStreet 7 with other students and 3 students are a customer:

 

SesameStreet 7 | Student #1

SesameStreet 7 | Student #2

SesameStreet 7 | Student #3

 

Now Student #1 moves to Maryland 10 since only one record exists for SesameStreet 7 all are updated to Maryland 10:

 

Maryland 10 | Student #1

Maryland 10 | Student #2

Maryland 10 | Student #3

 

One UPDATE just messed up your entire DB. I could even start adding addresses and then change them to my current address, now all your shipments are send to my home address (while I didn't pay for any of them).

[mikosiko]

@ignace: seems that we are talking exactly about the same or we missed each other point at all.. I'm not sure 

 

 

[ignace]

I then misinterpreted what you meant, sorry.