0

Consider a user can have multiple addresses. There is a users table, an addresses table, and a user_addresses table that links addresses and users with a many to many relationship.

Users can define what their default address is.

Is it best to have the users table have a default_shipping_id column linking to the appropriate entry on user_addresses, or should the user_addresses table have a is_default column?

Users table

ID name default_user_address_id
-------- -------------- --------------

v.s.

User_addresses table

ID user_id address_id is_default
-------- -------------- -------------- --------------

What's the better approach here?

jaster32123
  • 1

3 Answers3

0

It doesn't matter.

As you have to get the message from the second table, you always have to inner join it , and add a where clause, to get the right one.

So the only thing would be size.

A boolean needs 1 byte.

Your default_user_address_id would be bigint which needs 8 Bytes, so as most people have only 1 address or maybe two, you would save space with Booleans.

So my vote would be for number two, as it would save size.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
nbk
  • 8,191
  • 5
  • 13
  • 27
  • My primary issue with going with the second option is, and please let me know if it's unfounded, is that when updating the is_default I have to first remove is_default on one record (set to 0), and then perform a second operation to set another to true (1).However, in the first case, I only have to change the referenced ID - as far as I can tell. – jaster32123 Mar 06 '21 at 21:18
  • yes that is true, but that is a small problem to run two update querys. first set all to Fqalse and then set one to true. But size of the database can be when you got lots of rows much more of a problem. – nbk Mar 06 '21 at 21:27
  • In my opinion it does matter. It’s not just a matter of size or even performance but of correctness. By using a foreigner key the database constrains that the address must exist. – Colin 't Hart Mar 06 '21 at 21:28
  • However in the second case I could easily get all addresses the user has and populate a dropdown (and include the default) without ever having to join the users table. Damn, don't know which way to lean – jaster32123 Mar 06 '21 at 21:31
  • the constraint is there with user_id so a correcaltion is already there and all adress exist for that user – nbk Mar 06 '21 at 21:32
  • @jaster32123 as i wrote in my first sentence it doesn't matter, the qierys a quite simple for all cases, choose one and go for it – nbk Mar 06 '21 at 21:37
  • @nbk haha if I spent half the time doing instead of overthinking what I should do I'd have probably landed on Mars by now... xD – jaster32123 Mar 06 '21 at 21:38
0

I’m strongly of the opinion that it should be on the user table as default shipping address with a foreign key to the address table. That way you have made the database ensure that the address can not be deleted which could be done if you had implemented it with a flag on the address table.

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
  • Hmm I like this point. I looked into it a bit deeper and it seems that my 2nd approach wouldn't support constraints to ensure only one is_default is true, at least in MySQL. There seems to be a workaround but I guess its not actively advised: https://stackoverflow.com/a/45087858/7410951

    What are your thoughts on creating a third table, user_address_default, which could then be constrained?

     – jaster32123 Mar 06 '21 at 22:02
0

Like nbk mentioned, it doesn't really matter much between the two. The only thing I'll say about the first is it's a little denormalized and can be logically confusing to store the address_id in two places, the Users tale and the User_addresses table. I'd personally opt for the second design and not worry much about making two updates, since an extra update on a single row will be inconsequential.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • I've read a few other opinions on storing addresses and I think I'll make do without the middle many-many table - just store a user_id on the addresses table directly. Though as far as I can tell, the question would still be valid as to using default_id vs is_default... – jaster32123 Mar 06 '21 at 21:36
  • @jaster32123 Even in that case I'd probably still opt for storing the is_default field on the Addresses table since it is specific to the Addresses entity / object. That is normalization, where storing it on the Users table is denormalization. It's really splitting hairs though since it's one ID-based field. Also, side note, if you do get rid of the many-to-many User_addresses table, you also lose a level of normalization and can end up with duplicate data in the Addresses table, since theoretically more than one user can share the same Address (e.g. apartment, etc). – J.D. Mar 06 '21 at 21:50