CODEWITHSUNDEEP
mysql
olleh
olleh

Reputation: 1277

User Address relationship MySQL - Database Design

I'm getting confused on how I should design my database schema for an Ecommerce site. I want to create an address profile for my users.

By default, the shipping address is the same with the billing address. The user can't create new billing address, but he can add more shipping address. If there is no shipping address then it will use the billing address as default shippign address..

My problem is that, whenever I change my shipping address or billing address, I must first toggle the "active" field for both of my tables ("billing", and "shipping).

Example

Billing Address
1. Miami 

Shipping Address
1. Miami  -(active)
2. New York
3. Boston

When I set my Shipping address to new york, I don't like the idea of updating the rest of the fields to 0 and then setting new york to 1.

Is there a better implementation?? Should I just create another field in my users table which indicates the selected address?? Should I merge both Address tables to form one address collection only?

Schema User = id, name

Shipping Address = id, user_id, complete_address, active

Billing Address = id, user_id, complete_address, active

Upvotes: 0

Views: 1787

Answers (1)

Ethan
Ethan

Reputation: 2784

Users
id, name, default_shipping_address_id, ...

BillingAddresses
id, user_id, street, ...

ShippingAddresses
id, user_id, street, ...

Is how I would do it. Use the latest billing address for the user. Also make sure you are storing all the meta-data like date-created, date-modified, etc.

Upvotes: 2

Related Questions