Many to many in many ways? (Search subcategory)

I have a many-to-many relationship, linked by a table " farmer2crop

" linking farmers to crop types (eg rice, wheat, corn, spices, etc.).




However, some cultures require further search for subcategories, eg. spices (cumin, pepper, etc.)

Partly I think all spice subtypes should be part of the crop table, and maybe there should be a parent category, but the client wants the spice to be secondary and most crops are not parent.

So I can have a table with many for many sides connecting farmers2crops:



But it seems a little confusing, are there any suggestions for better design here - or is this a better compromise?

Thanks in advance.


source to share

2 answers

Logically, you should have the following tables:

FARMER (farmer_id, ...)

CROP (crop_id, farmer_id, ...)

CROPSUB (cropub_id, crop_id ...)

With these three tables, you can connect across all connections (three dots represent other possible fields in the table).

I hope this is helpful to you.



I would forget the parameter farmers2crops2spices

too complicated for IMO.

Simple option: add column subcategory

table + crop.subcategory_id

(NULLable). However, you can get tables subsubcategory

, subsubsubcategory

etc. (+ crop.subsubcategory_id


But my guess is that you will need a nested set for your crop categories in the long run . I suggest using an ORM like Propel (if you are using PHP), which will abstract all the heavy stuff to use such a structure effectively.



All Articles