+------------ --- ---+
| Assembly options |
+------------+------------+----------+------------+---+---+---+ --- +--+
| assembly ▼ | unit cost | quantity | total cost | 1 | 2 | 3 | |50|
+------------+------------+----------+------------+---+---+---+ --- +--+
| VSD55 | £10'000 | 2 | £20'000 | 1 | 1 | | | |
If somebody handed that quote to me, my first question would be "What's option 1 for the VSD55?" The answer would be "I don't know." That information isn't on the quote. In the unlikely event that person got to field a second question, that question would be "What does it cost?" Again, the answer would be "I don't know." A very disturbing silence would follow immediately, during which the person who handed me the quote would imagine how much better it might feel to be run over by a train.
Options must be line items on the quote, along with their unit price, quantity, and total price. Options must be named, not numbered. They should appear directly under their parent assembly, too, not scattered all over hell and half of Georgia.
If you want a shot at my money, you'd better make it crystal clear what I'm supposed to be getting for my money.
There's nothing (much) wrong with 50 check boxes on a user-interface form. That makes it easy to pick options. But the UI code should read the checkboxes and insert the right information into normalized tables.
So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?
I think it is better choice to have of purchase_id
as FK
in SALE_ITEM
table in oppose to product_id
, because sell is something related to PURCHASE
and PRODUCT
both and this option offer you this relation, you can get the corresponding product_id
and information related to PRODUCT
by making a join in sql query.
Or, should I force the client to sell the old lot before selling the new ones?
Don't add restrictions with your design, let them give flexibility to choose the way they want to. Even this part can be handled with sql query incase needed.
Best Answer
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
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
Do the same for all your markets. Think about using
VIEW
s 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.
ENUM
type. Don't! They are evil©, and here's why. Never use it or that other invention of Beelzebub, theSET
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
ENUM
s orSET
s. 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:
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.