I am new to Wordpress development but have experience of other dev.
I would like to store information that in a more trad setup would mean setting up some tables with primary keys and then a joining table for a many to many relationship, but I am not sure this is the way to go with Wordpress as many of the forum posts I have read suggest that it would be best carried out with custom post types and custom fields.
I want to record a few fields in a 'Company' table and a few fields in a 'FiscalYear' table. One of the fields in the 'FiscalYear' table would be a drop-down of the available companies in the 'Company' table.
There would also be a third and fourth tables, one which would be like 'post categories' where each company could have more or one entry which would be a type of product that it supplies and the fourth with a many-to-many relationship between 'Company' and 'Categories' so that each company can have multiple categories.
I would then want to return that data by category, listing the companies who have that category and returning some of the 'Company' table fields as well, by year.
How should I go about setting this up? Is custom posts and custom fields the way or do I need to set up custom tables in the db backend?
Also, should this be controlled from the theme or a plugin? Any advice on how to do this as well would be gratefully received.
If I've missed anything, please just let me know.