2

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

0

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.

  • Hi janh, thanks for the quick response. For info, there will be no correlation between individual weather reports. They will always be standalone so fairly simple in that regard. I'm bouncing options 2 & 3 in my head at moment and may go the way you suggest. Option 1 gets messy I think in that there's either an overhead in generating unique keys or it gets complicated querying arrays when updating them. – Mick R Nov 4 '17 at 18:24
  • Well, will there be no correlation NOW or have you decided that there should NEVER be any correlation? For option 1, you COULD store arrays as serialized data in post meta fields. It's rather simple to get running, but it just feels dirty and wrong. And it'll make sure that you'll avoid doing any reporting based on the data ;) – janh Nov 4 '17 at 18:35
  • 100% no correlation. Each report is private and unique to the post author, and no relationship between each authors individual reports either. Where I pause on the post_meta option though is how to name the keys for each array - unique or duplicate? If unique, would I need to query the whole table checking which ones have already been created before I generate the next or, for example, if duplicate would I need to query/check each and every weather report array generated by the author if they want to update or delete? – Mick R Nov 4 '17 at 18:42
  • Even private & unique reports could still be correlated, show the author trends in his reports for example. WP does allow you to add post meta fields with the same name, but I've never liked that. Quick & dirty: add a field for "next id", and build your own little RDBMS with primary keys and auto_increment - or just take the one that you already have and leave post meta fields alone ;) – janh Nov 4 '17 at 18:48
  • Thanks janh, yep, option 1 is off the table I think. I'll focus on the custom table vs parent/child approach for the moment. My knowledge on the inner workings of WP db querying is quite rusty so hard to know if finding child posts is more efficient than querying a separate table - might try both as a test and see how it goes. I'll leave this question open in the meantime - tx again. – Mick R Nov 4 '17 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.