sql relations, connecting several categories

Question

I want a single valuation to be able to have several categories, (or just one). The category table is one I'm filling pre hand with categories like wine, etc. So my question is how can I make this possible? Will I need another table?

Thank you!

Answer 1

So a single Valuation has many Categories, and a single Category has many Valuations?

This is what we call a many:many relationship and the typical way of dealing with it is to have another table, ValuationCategory*, that has a pair of Id- the ValuationId and the CategoryId

In Valuation and Category the Id are primary keys. In ValuationCategory each Id is a foreign key to the two parent tables. In ValuationCategory each Id can appear more than once but a pair of id is unique and is hence often used as a composite primary key (two columns comprise it) for the ValuationCategory table

Valuation 1 ----- M ValuationCategory M ----- 1 Category

ValuationCategory can have its own columns too, like "the date that this valuation as first recognised in this category" perhaps. The more columns you add to ValuationCategory the more it starts to become a "table in its own right", like Order is its own table between Person and Product (many person buy many product, an order/orderdetail type setup maps the two but stores a bunch of other stuff related to the actual ordering/fulfillment process) so there's a point where ValuationCategory may cease to be a "table that just decomposes a many:many relationship", probably around about the point that it can answer questions that are interested in more than just meta info (For example - "when was the valuation categorised" is just meta, but "how many categorisations with a reason of 'correction' does author X make per year?" - if VC knows the author, the reason, the date it's acquiring more knowledge of an outside process than just "x and y are related").

At the point that it becomes a "proper" table in its own right, you might want to give it its own unique ID column; you don't have to, but if you're going to be treating it as a standalone entity like a Valuation or Category, you might want it treated the same

All in, I'm sure you've used such a table without even realizing it, it just had more columns

---

_{* which to me makes more sense than CategoryValuation, but it depends on your perspective; if categories are the important things and valuations are not then CategoryValuation works. If the Valuation is the object of focus it reads better to name it first..}