How to replicate data storage and querying using Wordpress. Custom posts/fields or Custom db tables?

Question

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.

Many thanks!

Answer 1

This question is too broad for a concise answer but generally speaking I'd recommend:

• A custom post type
• Custom taxonomies (for grouping)
• Custom fields for arbitrary data (a plugin like Advanced Custom Fields will save a lot of time)

You can query by both the taxonomy & custom fields using tax & meta queries. There's a premium 3rd party addition for ACF that converts custom fields to real tables if you're working with huge amounts of data.