This is a simple example:
Assume I've got a customer table which has a one-to-many relationship using the address table (a person might have multiple shipping addresses but a shipping address are only able to fit in with just one customer).
Customer Table ------------- customer_id (PK) Address Table ------------- address_id (PK) customer_id (FK)
Now I would like each client to have the ability to specify a default shipping address.
I'm able to think about two ways but have disadvantages.
Give a Boolean column towards the Address Table known as "is_default".
- Choose queries are pretty straight forward
- Straightforward for some individuals to know when they begin to see the DB model
- The applying needs to enforce and keep the "just one row could be a default" constraint.
- Upgrading the default area is really a discomfort since it necessitates the application to check on and totally reset the prior default option.
Give a column towards the Customer table known as "address_id" and turn it into a foreign key (also allow nulls just just in case no address is available).
- Simple to update the default address when the user decides to alter it.
- Database keeps the "just one row could be a default" constraint.
I must give a new indexed column towards the Clients Table each time I choose to then add type of default metadata.
Appears just like a hack
My real question is, it is possible to standard way additional type of scenario that i'm looking over? Obviously you will find other available choices (maybe creating an EAV default options table?) but I'd would rather ensure that it stays as easy as possible since the change has been designed to a current code base so I'd rather not break anything.
(maybe creating an EAV default options table?)
I'd do that, particularly if you are concerned about breaking existing code.
Customer_Defaults ------------------------------------ customer_id PK, FK -> Customer default_shipping_address_id FK -> Address
Is not that tidy? By tugging the entire factor right into a separate table, you depart the present tables alone. If you are with a couple type of ORM layer, the item over this new table could be queried for directly, and you can walk towards the Address object. You don't need to even introduce the brand new resist the present Customer or Address objects.
Whenever you grow new needs, consider new tables. Listed here are two various ways to approach your condition. The very first is less stringent compared to second. (I'd make use of the second.) I'll assume "Address Table" is known as "customer_addresses".
create table default_shipping_addresses ( customer_id integer primary key references customers (customer_id), shipping_addr_id integer not null unique references addresses (addr_id) ); create table default_shipping_addresses ( customer_id integer primary key references customers (customer_id), shipping_addr_id integer not null unique references addresses (addr_id), -- Add a UNIQUE constraint on customer_addresses (customer_id, address_id). -- Since address_id is the primary key, it's unique, so (customer_id, -- address_id) will also be unique. But you need the UNIQUE constraint to -- allow a foreign key to reliably reference it, even in MySQL. foreign key (customer_id, shipping_addr_id) references customer_addresses (customer_id, address_id) );