Database Question with one-to-many relationship with default

Question

I have a User table, and an Address table. The User can have many addresses, so the Address table entry has a foreign key to a User.

My Question is: The user can have many addresses, but can label only one as it main address (for shipping purposes). Is it better to add a foreign key to User named "main_address" which links to one of his addresses, or to add a column to the Address table named "is_main" (0 by default for all addresses, and 1 for the main address)? By better I mean speed and storage wise :)

EDIT: As suggested, I added the foreign key to User, but this is problematic. If the User and Address are not in the database yet, then it results in Circular dependency: A User can't be added without the Address, and the Address can't be added without the User. Thanks to everyone!

Answer 1

Having a foreign key on the user table, that is optional to avoid an interdependency that prevents adding records to either, is probably the cleaner solution. It avoids having to deal with preventing multiple main addresses for a user, takes less space, and makes joins to use the main address' information a little simpler.

Uniqueness is built in to foreign key the design. With an "is_main" design, the simplest prevention is making the field 1 or null (instead of 1 or 0) and putting a composite unique key on (user_id, is_main); and then to change the default you'd have to null out the old default before setting the new one to 1.

The foreign key field takes about a flat 4 bytes (assuming int pk for addresses, and ignoring nullability); is_main takes at least 1 byte for each address a user may have... if there are other bit fields, it could be smaller, but the space concerns are really trivial.

To get a user with their default address:

SELECT * 
FROM user AS u 
LEFT JOIN address AS a 
   ON u.main_address_id = a.address_id 
WHERE u.user_id = ?
;

vs

SELECT * 
FROM users AS u 
LEFT JOIN address AS a 
   ON u.user_id = a.user_id 
   AND a.is_main = 1 
WHERE u.user_id = ?
;

...that AND is_main = 1 may seem like a trivial thing, and in a lot of cases it will be, but in a more complicated query it is the kind of thing that can mean not being to take of an index.

However, an upside to the is_main design: If you already have the user_id but not the main_address_id, you can get the main address information with a simple (no join) select from only the address table.