Re: Modeling bill/ship addresses - Mailing list pgsql-general

snacktime wrote:
> I've been going back and forth on the best way to model this.
>
> A user can have one to many bill and ship addresses.
I would think that one billing and many shipping would be most likely.
eg head office for billing and shipping to each branch office.

> An order can have one bill address and one to many ship addresses

An order would have one bill and one delivery address. Think of an order
as a single entity - it gets boxed and delivered to the address - if a
customer wants two products each sent to two different addresses, then
it is two separate orders, one delivered to each address.
If an order has two delivery addresses then how do they decide which to
take it to?

> Let's assume I have a single address table, with an address_type
> column that is a foreign key to the address_types table.

Personally I wouldn't distinguish between type of address, they simply
choose the billing and shipping from the list of addresses that has been
entered. Also some customers will use the same address for both billing
and shipping. Does the same address need to be entered twice? or will
you let them select the same address for both?

I would have two columns in the customer table - one for default billing
and one for default shipping, which holds the primary key for the
selected address.

Similar with the order table - one for billing and one for shipping.

When an order is created the default billing and shipping address gets
copied from the customer table and they have a chance to select a
different address for that order. Or you can just have them select each
address as they do the order.

> Now to create the relationships between addresses and users/orders.  I
>  could create a join table for holding the addresses that belong to
> orders.  For example table order_addresses that has order_id and
> address_id columns that are foreign keys on addresses and orders.
>
> But what about just having two foreign keys in addresses?  order_id
> and user_id?  Or is there a rule against having a null foreign key?

I see no need for joining tables. This is a few different one to many
relationship - one customer to many addresses, many orders to one address.

You will want a customer_id for each address which allows you to list
the addresses they can choose from. You could also use this to link back
to the customer table from the order. That would be instead of storing
the customer_id with the order table as well.

For an order you have the billing_id and delivery_id which you use to
get the addresses to put on each order. You can either get the
customer_id from the address table or store that separately in the order.

> Also, is there a good database independent way to make the address
> immutable once it's created?  I don't mind doing it at the application
> level actually, as I'm using a MVC framework that makes it easy to
> define that logic once in the model instead of spread out all over the
> codebase.

Either handle this at the client end (as in having no code to update the
address table) or you can use a trigger or rule at the db end to prevent
updates to the address table. It does seem extreme though - what if the
address was entered wrong? Can they delete the old one and re-enter it
correctly? Or do they have to approve the address as being correct at
which stage it is immutable?



--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz