-1

My original table which is not normalised looked like this:[1]: https://i.stack.imgur.com/NbKV4.png

Now after following the conditions of each form, I managed to separate the table into 3 forms which look like this[2]:https://i.stack.imgur.com/b414X.png [3]:https://i.stack.imgur.com/haz2Q.png [4]:https://i.stack.imgur.com/CpWXw.png

My aim is to make the database into 3NF, is this the case? If not, please give me some advice on any amendments needed, thanks.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Habibullah Ahmadi
  • 1
  • 4
  • I would probably keep the cost with the product and have a separated table called inventory to handle stock – Prix Jan 02 '23 at 15:41
  • And for the category I would do something like `id, name, parent_id` so if a category have a parent_id u would include the id otherwise leave empty – Prix Jan 02 '23 at 15:43
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) – philipxy Jan 02 '23 at 23:45
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when a process allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your reference. [ask] [help] [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) Basic questions are faqs, research before considering asking & reflect research. – philipxy Jan 02 '23 at 23:48
  • [How to decompose the schema into 3NF?](https://stackoverflow.com/q/33703457/3404097) – philipxy Jan 03 '23 at 00:40

1 Answers1

-1

You went from

(product_id, product_name, product_cat, product_subcat, cost, stock)

to

products_details(product_id, product_name, stock)
products_categories(product_cat, product_subcat)
products_costs(cost_id, cost)

That's good but not enough according to me. And not complete either. Here's a first solution…

product_details(product_id*, psub_id@, product_name, cost, stock)
product_categories(pcat_id*, pcat_name)
product_subcategories(psub_id*, pcat_id@, psub_name)

…where * indicates the primary key, and @ the foreign key. Another possible second solution is

product_details(product_id*, pcat_id@, product_name, cost, stock)
product_categorisation(pcat_id*, pcat_parent@, pcat_name)

This later is good only when not all categories have subcateries (not sure my writing is clear here, sorry.)
Also note that I didn't put costs in a table by their own because I don't think they are such independant. However, I don't know the context you are modeling.

gildux
  • 448
  • 6
  • 12
  • "That's good" It is not good. It loses the relationships on product & cat, subcat & cost. And normalization to higher NFs does not introduce ids. Your designs have similar problems. – philipxy Jan 03 '23 at 00:54
  • Your comment does not address the problems mentioned in my comment. The problems with your designs are not what you left together but how you separated things. (Observe from the example data that subcat does not determine cat.) When I say "loses relationships", I mean that certain projections of the original table cannot be reconstructed from the bad designs--so they are bad. Anyway the example data plus product id being a CK means the original is in 5NF. – philipxy Jan 03 '23 at 04:36
  • "Observe from the example data that subcat does not determine cat." Nor cat subcat, of course. So your designs cannot reconstruct original (product id, cat, subcat) subtuples. – philipxy Jan 03 '23 at 05:13
  • The subtuples (product id, cat, subcat) are exactly represented by `(product_id*, psub_id@)` in my answer, that's why I don't understand your comment. After a rest, I guess you were attacking the OP proposal (did you noticed I wrote "you went from … to …"?) And, well, when I comment "that's good but not enough", it's not a full validation (sorry for the french construction "yes-but" of my sentence), I mean it's a good starting point (avoid redundency, find candidate keys, etc.) I didn't imagine someone would stop at the intro. – gildux Jan 03 '23 at 10:51
  • Again: Your designs (like the question post design) cannot return the projection of the original on product id, cat & subcat. I just gave the specific lack of FDs that leads to that.--With the given data a subcat does not always give a single cat, hence those designs cannot give back the cat a subcat & product appeared with in the original. If you gave a correct justification for your decomposition you'd butt up against that. "And normalization to higher NFs does not introduce ids." PS Writing something wrong is poor writing. Writing something then contradicting it is poor writing. – philipxy Jan 03 '23 at 12:04