1

I want to ask a general question. We are getting data from different stores like magento, woocommerce, amazon aws, shoppify and many others. Data like customers info, orders info, products info is being fetched. All the stores are returning data that is bit same, bit different. Here are two database designs that we are proposing:

  1. Use different columns for every store like magento_customers, shoppify_customers but this do not seems right.
  2. Make a single column for customers from all sources. fill the fields that has data and make others null, like magento returns group_id, but woocommerce do not, so fill group_id by '1' lets suppose and when it's null, keep it null. I am a junior developer, and this is my first commercial project so I'm little hesitant.
  • 2
    Just because Magento made a mess of designing their system, (see also posts here on the EAV model), doesn't mean that you have to! :-). You have stores - you won't have thousands of them - have a table for each one with its own design which works for them - you can then use VIEWs to gather data and summarise! – Vérace Oct 2 '17 at 8:42
  • this options seems fine, I will discuss with my seniors.. – Ahmad Raza Oct 2 '17 at 10:32
2

It sounds like magento customers, shoppify customers, etc. are subclasses (subtypes) of the customer class. This is the terminology in object modeling.

In ER modeling, the terminology is usually generalization/specialization.

There are two techniques often used when designing tables to fit this situation.

One is single table inheritance. Store all the customers in one table, with a customer_type field, and all the fields for all the types. Leave fields NULL when they don't apply to a particular customer.

Another is class table inheritance. Have one table for customers with all the common data, and only that data. Have separate tables for each subclass (subtype) of customer, with data that is specialized to that subtype. In the subclass tables, don't generate a distinct id for the rows, because you don't need to. Use a copy of the customer id as both the PK for the specialized table and an FK to join with the correct row in the generalized table.

Joining specialized data and generalized data is pretty fast, and fairly simple.

With either technique, the problem you originally posed goes away. You can make an FK reference to the customer table, and access both the generalized data and the specialized data.

If you search on all the buzzwords I've given in here, you'll get lots of useful articles on the web that treat the subject in more depth. You can also explore the tag right here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.