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.
