Database Design – Different Types of Columns

database-design

Maybe the title is misleading but I'll try to explain what I'm trying to do.

I'm building an e-commerce site. I have my products and my order.
I want add constrains on how the order will be deliver to the customer depending on each product.

For example if I sell a big and fragile product I want to restrict it from being send with courier, the customer should only have the option to take it from the shop.
Or a specific product can only be sent to limited countries.

I thought I could create an extra table related to products.

enter image description here

That way I could know which types of constrains each product have.
And I have no limitation on how many extra type of restriction I can make.

For example 

restrictions(1,124,'post.courier',false)

But if do this,

restrictions(2,1322,'country','Greece')

the value field it's a different type.

My second thought was to use a field restrictions type TEXT and pass inside both values and restriction in a JSON format or XML

Another thought was to create separate tables for each kind,

enter image description here

and join them all, but it didn't seem proper.

What would be the best in my case?
Is there a better way I could design my DB?

Best Answer

Would it not be safe to assume that your products might have more than one restriction?

In that case, don't you need an intersection between product and restriction (type)?

Consider this ERD:

enter image description here

Note that restrictions are shown using an entity-sub-typing model. You can keep distinct restriction value types appropriate to the kind of data that you require. At the same time, the individual specific restriction types share a key space with the restriction header table. That makes the intersection: PRODUCT_RESTRICTION easier to define and use.

There are different ways of doing it, but you'd probably want to consider including a partitioning attribute in the RESTRICTION table.

Walter Mitty suggests:

If you want to model restrictions as a case of subtyping, there are design patterns for this case. There are two tags over in StackOverflow where the info tab might help you:

Joel Brown responds:

The restriction model I have illustrated is one common way of implementing subtyping. There are other variations.

Related Question