Which is best in the following scenario : post_meta vs custom table vs parent/child posts

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:

1. Primary data stored as custom fields for each private post in the post_meta table.
2. 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

or

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.

or

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.

Thanks,

Mick

As you will probably want to eventually do some reporting on the data, e.g. print a temperature average from this month last year in area X, I’d opt for custom tables from the beginning. I’m saying tables, because I don’t see a reason why you wouldn’t put the locations into a custom table as well. If you ever want to do something like calculate distances (and you have the lat & long for your locations stored), this will make your SQL much cleaner and easier to write.

Post meta data isn’t normalized, and will be painful to work with if you have more complex scenarios. It also doesn’t contain useful indexes, as it has no data type associated. This might not be a problem initially, though, as your data set is still quite small.

The only thing FOR meta fields in this scenario is that you could use WP_Query directly and without a lot of hassle. But I’m rather confident that you could add some functionality to wp_query via filters – and you’d have to use those fore advanced stuff anyhow in order to manipulate the auto generated SQL.

That said, it also depends on how quick you (and any other people that might work on it with you) are when developing with a special table vs some pure WP based solution. You could always start with 3, and if the project takes off, you invest a bit more time to make it nice and clean and go for 2. The data conversion shouldn’t be that big of a problem.