Application and Database Design


#1

Hi,

I am begining the design of an order management system, and had a few
questions
about the database design. For example: I have a customers table and an
orders
table, with a one to many relationship. Now if i change the customers
address
after the order has been created, it will be updated in any of the past
orders,
and I do not wish this to happen. Is the best way to deal with this to
store the
customers information in the order table, or is there an easy way to set
up some
sort of versioning system for the customer within rails?

Thanks,
Ryan


#2

I think that the easiest way to do this is create an addresses table
with relationships to both the customers and the orders. It would have
many addresses for both customers and orders (one to many relations).
This is how you set up a versioning system. It also requires that you
put into the addresses table some indicator of a preferred address for
the customer so that it is easy to know which is the default.

Others may have some other ideas.

Ben Orchard


#3

On 4/14/06, Ryan L. removed_email_address@domain.invalid wrote:

Thanks,
Ryan


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi Ryan

From your description, I think the simplest, best solution would be to
“stamp” the current customer address into any order created. That way
the address “stays” with the order, independtly of the client. You can
however retrieve all past orders of the client, and hence all his past
addresses, so you in effect get all the benefits of versioning without
the costs.

Regards,
Alder


#4

Ryan L. wrote:

I am begining the design of an order management system, and had a few
questions
about the database design. For example: I have a customers table and an
orders
table, with a one to many relationship. Now if i change the customers
address
after the order has been created, it will be updated in any of the past
orders,
and I do not wish this to happen. Is the best way to deal with this to
store the
customers information in the order table, or is there an easy way to set
up some
sort of versioning system for the customer within rails?

Check out the acts_as_versioned plugin


Josh S.
http://blog.hasmanythrough.com


#5

An address table is one good idea, but keep in mind that if you want to
meet your first goal of keeping the addresses used on invoices, you’d
have to save any “edited” address as a new address entry and retain
fields for marking which ones are “current”, maybe one for “which” of
the users’ saved addresses this is (home, work, gramdma,etc.), and
another for the version, so that an order going to Mary’s home address
in April stays linked to the April home address.

A much simpler but less flexible solution is to have address fields in
the order itself and copy them from the User table at the time the order
is saved. This way addresses and even users can be modified and deleted
to your heart’s content, but the address info stays with the record of
the order.

The disadvantage to the simpler approach is that it doesn’t offer a
clean way to generate a report on all the orders shipped to a given
address, for instance. But then, plenty of e-commerce sites get by just
fine without such things. It’s all a matter of how much complexity you
reasonably expect to use.

Ryan L. wrote:

Hi,

I am begining the design of an order management system, and had a few
questions
about the database design. For example: I have a customers table and an
orders
table, with a one to many relationship. Now if i change the customers
address
after the order has been created, it will be updated in any of the past
orders,
and I do not wish this to happen. Is the best way to deal with this to
store the
customers information in the order table, or is there an easy way to set
up some
sort of versioning system for the customer within rails?

Thanks,
Ryan