I Need Help With My Database Design for Product Management

Question

I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products and see their calculated cost for our different marketplaces. I plan on expanding the functionality in the future but for the moment our goal is just product pricing and dimensional data lookup.

How does this design diagram look to you guys? Is there anything that you would recommend me to add or change? I am open to criticism as this is my first professional database project.

Thanks

Here is the sql file https://github.com/ryanbbernstein/Database/blob/master/Product%20Database.sql

Answer 1

This is just an "at-a-glance" high-level overview.

• you have "ID" as your primary key for all your tables. Despite this being anathema to purists (pace Joe Celko and Fabian Pascal), personally, I have no problem with the use of such surrogate keys. However, I would advise that you rename these fields "product_id", "supplier_id" &c. for two reasons

  • ultimately it will make your SQL easier to read, and (relatedly)
  • will greatly help debugging - i.e. if you get an error saying that "... error with field ID = 123456...", you won't know to which table it is referring, whereas with my way, it's obvious.

• Your product table is already becoming quite wide. If I were you, I'd split it it up this way.

You have various markets AFAICS (Amazon, FBA, EBay &c.). What happens when you have 47 markets? Your product table will be horrendous! :-). You should move these markets out into separate tables. Have an "amazon" table something like this

CREATE TABLE amazon (amazon_id int PK, product_id int FK, amazon_price... Other Amazon fields &c.).

Do the same for all your markets. Think about using VIEWs to combine this data for a given product later - as well as SQL. Rule of thumb: Tables should be like women, tall and slim not short and fat! :-)

You could also think about doing the same thing for your alternates. What happens if you have more than 1 for a given product? Putting these alternates in a separate table will allow you that flexibility down the road.

CREATE TABLE alternate (alternate_id int PK, product_id int FK, alt_supplier int FK, &c...)

• You make use of that MySQL abomination, the ENUM type. Don't! They are evil©, and here's why. Never use it or that other invention of Beelzebub, the SET type.

Firstly, they breach Codd's rules. Secondly, they are non-standard. Imagine, 5 years down the road, your business has taken off and you want to upgrade from MySQL (any change from MySQL is an upgrade IMHO :-) )? Your system will require a complete rewrite rather than just tweaking if you've used ENUMs or SETs. They're a bit like drugs, great at first but coming off them is difficult and painful.

My final two points are my own preferences/biases (take your pick :-) )

• you have a mixture of singular and plural table name (products, log_book). Pick one and stick to it - my own preference is for singular (1 exception, orders, for obvious reasons - never use SQL keywords as tablenames).

• if you are only starting out on this project, I would strongly urge you to use PostgreSQL instead of MySQL. Many reasons for this:

  • CHECK CONSTRAINTs
  • Window (aka analytic) functions
  • Full support for set operators
  • CTEs (Common Table Expressions)

Having these built into your RDBMS will save you a lot of time and money in the long run rather than having to implement them yourself later.