Jump to content
Larry Ullman's Book Forums

Mysql Tables Design


Recommended Posts

This is not really a book related question but more on a question of design for my tables. If i would like an expert opinion because this could be very costly for me if i was to make a design mistake and have to come back to recode later.

 

I have a basic item table on my website which stores all item details, title, cost, quantity etc. Now each item can have more than one local shipping or international shipping.

 

The problem i have here is that if someone had to list 100 of there products they would have to fill out these local and international shippings 100 times. So say they had 3 local shippings and 3 international shippings that would be 6 different shippings per product which would mean they would have to copy this out 600 times which seems ridiculous.

 

So what i have done is build a shipping module which means you create one shipping module select which local and international shippings you want, then when you list your item you can pick the shipping module you want to associate with it. This would obviously be a time saver and a bit of common sense.

 

I have is the local and international shippings which could be 3 of each per shipping module are saved to two separate databases one for local the other for domestic as both have their own individual criteria.

 

So how should i make my databases if the local and international shipping databases would be the same for a shipping module as to an Item?

 

There are two solutions i have now and i am wondering which is best? This is where i need your help...

 

Solution 1

 

Create separate local and international shipping databases for Item and Shipping Module.

 

For example

 

CREATE TABLE shipping_local (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
item_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_local_item_idx (item_id ASC)
)ENGINE=InnoDB;

CREATE TABLE shipping_international (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
item_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_international_item_idx (item_id ASC)
)ENGINE=InnoDB;

 

CREATE TABLE shipping_local (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
shipping_module_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_local_shipping_module_idx (shipping_module_id ASC)
)ENGINE=InnoDB;

CREATE TABLE shipping_international (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
shipping_module_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_international_shipping_module_idx (shipping_module_id ASC)
)ENGINE=InnoDB;

 

Solution 2

 

Create just one of each shipping_local and shipping_international database and add both shipping_module_id and item_id to each. So if an item put the shippings in local or international shipping database item_id would have the id of the item and shipping_module_id would be set as 0 rather than Null. And if the shippings where related to a shipping module, then the shipping_module_id would have the id of the shipping module and item_id would be set to 0.

 

For example

 

CREATE TABLE shipping_local (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

shipping_module_id INT(10) UNSIGNED NOT NULL,
item_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_local_item_idx (item_id ASC),

INDEX fk_shipping_local_shipping_module_idx (shipping_module_id ASC)
)ENGINE=InnoDB;

CREATE TABLE shipping_international (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

shipping_module_id INT(10) UNSIGNED NOT NULL,
item_id INT(10) UNSIGNED NOT NULL,
cost INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_shipping_international_item_idx (item_id ASC),

INDEX fk_shipping_international_shipping_module_idx (shipping_module_id ASC)
)ENGINE=InnoDB;

 

I know this is a long post but i really need to ask this, i have already made some major mistakes on my projects and they can take up to 6 weeks to repair or longer depending on what it is.

 

Edward.

Link to comment
Share on other sites

Okay well if neither of these solutions are okay please suggest what you would recommend here. I know nothing can be perfect but I need the best solution for now. I thought about this and it seemed like i should ask you first before proceeding. May be someone at Stripe could even give recommendation whatever you think best. I'll don't donate something to you at the end of the year for the help you have given me this year. Thanks Edward.

Link to comment
Share on other sites

Sorry for the delay, Edward. The heart of the issue here is database/implementation complexity vs. money optimization. The more generic you get with the implementation, the easier it'll be to use, but it'll cost money in the long run (due to lost sales because of overcharging for shipping or due to not taking in enough money to cover the shipping costs). So you'll need to strike the right balance for you and the business. 

 

Honestly, I think the best possible solution would be to use a third-party's API (e.g., UPS). It would calculate the shipping for you, based upon set criteria. 

 

But whether you use a third-party for calculating the amounts or not, you need to abstract this, starting with the relevant criteria. Each item would have a weight and a size that factor into shipping costs. I would store those with the items. The size would probably be represented on a scale from, say, tiny to bulky. Again, you'd store these criteria with the other item details.

 

Next is how you build up pricing for each item. Again, a third-party tool could probably do this for you based upon the combination of: total weight, total size, point of origination, and point of destination. Or you would implement your own system of calculating the shipping costs using those criteria.

 

And this leads to an interesting realization: you're not setting shipping costs on the items being sold but rather on the orders. A box so big that weighs so much--regardless of what's inside--that's going from X to Y is going to cost $Z. An item's size and weight is static, it's the trip and the total order that's dynamic. For this reason, your database should treat these separately (e.g., the item information stays with the item).

 

So I would try to design a shipping module based upon orders. You'd need to know shipping rates:

 

- A small box weighing up to X costs $Y to ship domestically.

- A small box weigh up to X costs $Z to ship to these international destinations.

- Etc.

 

You're still going to have a LOT of criteria, but you'll have fewer criteria and an easier system if you go this route, rather than identifying all possible shipping costs for all possible items. I would start by getting to know one simple example: shipping from, say, the UK to within the UK and to other international destinations. Figure that out for maybe 6-10 packages and you'll start seeing how this might go.

 

Also, I gather you've got kind of a marketplace model here. If so, your identifying generic costs based upon weight, size, origination, and destination could be easily reusable. You could also let your marketplace members implement a factor into the cost--.9 of base cost, 1.1 of base cost, etc.--to change how much they charge for shipping based upon the calculated costs you've already determined. 

 

Hope that's helpful. Let me know if you have any other questions or concerns about this.

  • Upvote 1
Link to comment
Share on other sites

I appreciate your long answer but its based mainly on the criteria of shipping a product. I have left out criteria from the question above and just quoted cost for local and international shipping because i do have extra criteria there to generally meet all in needs. Yes i am working on a market place but the criteria and shipping will be worked out by each seller individually, they could charge high or low for shipping gains or losses will fall on them.

 

There are other websites now that have run for more than a decade with the most basic shipping options and have sold millions of dollars, the details you have above are great i am aware of them and i bet there would be API's available but i just don't have enough fingers or a large enough brain to get all that done alone so i have to work on a more generic model.

 

So the criteria here is not the issue, the problem is if i have a shipping module or as ebay call them business policies for listing similar items. You can look up business policy online and i think you will understand what i am trying to implement, check this link.

 

http://pages.ebay.com/help/sell/business-policies.html

 

Now the problem is the business policy allows you to add local and international shippings just like you would if you were listing a regular item. So how to setup the databases is the question i am asking. Make two local and international for item and the business shipping policy or set them up separately. Or should this be approached in another way.

 

Using Solution 2 above would mean i would need the two foreign keys in the database for the shipping policy and the item

 

shipping_module_id INT(10) UNSIGNED NOT NULL,
item_id INT(10) UNSIGNED NOT NULL,

 

Is this correct? What is the correct thing to do?

 

I think solution 2 would do the trick but i think i should ask for a 2nd opinion just incase i am wrong. This is now the kind of thing i need to mess up right now, the clock is ticking if you know what i mean.

 

Once again i really appreciate you in helping me here.

Link to comment
Share on other sites

Larry i am going to send you a picture to your email now of the shipping policy, you can enter the same details on the item listing page for shipping just like this. Once you see the picture you will understand what i am doing.

Link to comment
Share on other sites

Larry i am sorry about this but i have decided i am going to scrap this idea and just allow for the shipping to made on the item listing page only. This makes things easier for you and me as we don't have to deal with this database issue it also cuts out 60 % of the code which i have to do. Thanks for your help.

Link to comment
Share on other sites

 Share

×
×
  • Create New...