DB Design help

[vchris]

Ok so then if I use your method with 2 address lines then I wouldn't need the city, zip code, country field right? What should the 2 line hold? Line1: 1 Test Street, Line2: ?

[Skemcin]

Sorry for maybe not communicating this clearly. I admit to really only ever seeing how I traditionally record this information and maybe have never really been exposed to other situations/challenges.The db should have it like this:adr_line1adr_line2adr_cityadr_stateadr_zipadr_county *adr_country **optionalYour business rule would define adr_line to always and only contain the street number, name and type - or post office box information. The adr_line2 field would contain information like apt, suite, building, or some other address number. So you would have:Ex 1adr_line1 = 1600 Pennsylvania Avenue NWadr_line2 = adr_city = Washingtonadr_state = DC adr_zip = 20500Ex 2adr_line1 = P.O. Box 1600adr_line2 = adr_city = Washingtonadr_state = DC adr_zip = 20500Ex 3adr_line1 = 1600 Pennsylvania Avenue NWadr_line2 = 1st Floor Oval Officeadr_city = Washingtonadr_state = DC adr_zip = 20500Does that help?

[vchris]

Yes very much, thanks!

[Skemcin]

Let me know if anything else comes up...

[vchris]

What is the best way to get an automated shipping quote?

[Skemcin]

If I understand you, you will need to have something that has a shipping cost matrix - like if an order is between $xx and $xxx then here is the rate. Or if the weight is xxxlbs then it costs $xx to ship. I've worked with systems that look at the cost to determine shipping and the weight. Then, it will look at the customer/shipping country to add any international shipping rates.I've not done anything thing that directly related to distance - which you would then use the ZIP code for that - of course you'd have to have some database of all the zip codes, and their latitude and longitude. From there you could determine the distance by applying a formula.Am I hitting on anything here that helps?

[vchris]

That would be one way of doing it. I was thinking of a way to get a feed from UPS or some other company to get the cost for the shipping.edit: On another note. When checking out, I ask the customer to enter his shipping name/address and also his billing name/address. The problem is that this database doesn't support that or it would be complicated. Should it just be 1 name but 2 addresses (shipping/billing)?

[Skemcin]

Technically speaking, yes - you should only "need" to track the customer once - then have multiple addresses. Because you are dealing with a many to many relationship (one customer can have many addresses and one address can be related to many customers [mom, dad, child, etc.] ) then you use a bridge table the customer_x_address table. This allows the any given address to be in the table only once and any given customer in the table once - the bridge table tracks all the many relationships - thats why you would ideally not have the customer's name in with the address table.In nay case, that should not affect what I understand you are trying to do. If all you want to do is figure out or "estimate" the cost of shipping something then you first need to figure out what drives your shipping cost - the distance, weight, or quantity?Does that make sense?