I’m looking for a bit of advice on which way to go with a development question.
I’m building a community site where subscribers create their own private posts. The data they enter falls into two groups:
- Primary data stored as custom fields for each private post in the post_meta table.
- Secondary data associated with, and unique to, each private post.
The secondary data can also be edited / deleted by the post author. When a private post is viewed the secondary data is also displayed. The following is an example of what I’m trying to achieve - not the actual scenario but the principle is the same:
Primary data : name, address.
Secondary data : user enters any number of weather reports whenever they feel like it. Each report consists of 3 fields:
- date (fixed format/length)
- time (fixed format/length)
- report (variable text length)
I’m trying to figure out the most efficient way of storing/retrieving/updating/deleting the secondary data. The delete option deletes the entire record, i,e. all three fields. The options I’ve considered so far:
1. Add additional post_meta custom fields and store each weather report as an array containing the three fields, and if so do I use duplicate keys (eg: 'weather') or come up with unique keys for each report eg: weather1, weather2, weather3 etc etc
2. Create a custom table and store each report as either an array with a unique or duplicate key, or possibly individual keys for each of the three fields with a common date/time stamp to group them, and an extra column for the author post ID as a reference to retrieve that particular users weather reports.
3. Create child posts (the initial private post being the parent), and for each weather report create 3 custom fields in each new child post.
Options 1 and 3 are tidy in that they adhere to the WP default structure, but option 2 might result in quicker DB queries? Having said that i’m drawn towards the parent/child option but TBH not sure. The solution has to be scaleable so assuming growth this might end up in tens of thousands of primary posts and any number of weather reports.
Any thoughts on the above, or other options I might not have thought of? Any and all suggestions welcome.