Wordpress site but non-WP data table integration
Hi - I have a client with a database of unresolved murders in Colorado from 1876 - Present. Unfortunately, this is an extensive list.
There are about 8 tables, all keying off victimID, and all containing possibly relevant information about the victim, the case, legislators and jurisdictions, investigative notes, concerned family members, victim photos.
I originally had the idea of using a CSV-to-Pages plugin so convert each Victim record into a page object in Wordpress, but now that I see the extensive structure of the original database, the data purist in me would hate for this process to forever break this structure.
However, the client is hoping for the ability of family members to login, modify records, add new records, upload photos - all things WordPress and maybe Buddypress could handle very well for us. Ultimately though, I'd love to be able to let people use this data in WordPress, but somehow keep an export option intact that would still maintain the table structure already in place.
I guess I'm just asking for suggestions on the best way to do this. Should I create custom fields for every database field so our pages all have this sprawling list of fields that can be edited? A bit clunky in WordPress, but likely possible to export as intact tables still? Or would it be easier to simply mimic the WordPress page function to render this table data, but keep it all as separate table fields for editing?
Advice, specific approaches, and any other suggestions would be much appreciated.
Thank you
Bill
There are about 8 tables, all keying off victimID, and all containing possibly relevant information about the victim, the case, legislators and jurisdictions, investigative notes, concerned family members, victim photos.
I originally had the idea of using a CSV-to-Pages plugin so convert each Victim record into a page object in Wordpress, but now that I see the extensive structure of the original database, the data purist in me would hate for this process to forever break this structure.
However, the client is hoping for the ability of family members to login, modify records, add new records, upload photos - all things WordPress and maybe Buddypress could handle very well for us. Ultimately though, I'd love to be able to let people use this data in WordPress, but somehow keep an export option intact that would still maintain the table structure already in place.
I guess I'm just asking for suggestions on the best way to do this. Should I create custom fields for every database field so our pages all have this sprawling list of fields that can be edited? A bit clunky in WordPress, but likely possible to export as intact tables still? Or would it be easier to simply mimic the WordPress page function to render this table data, but keep it all as separate table fields for editing?
Advice, specific approaches, and any other suggestions would be much appreciated.
Thank you
Bill
Jason C. Levine
I'm having a hard time visualizing the data based off the description above, so I'm also having a hard time coming up with a solution. My first instinct would be to simply create a user field to identify which users can see which cases and then use the wpdb class in page templates to create a complete CRUD system. So when a user logs in, read the case ID from their user record and then query all of the other tables to produce whatever data is needed for display.
ASKER
Yeah sorry I guess I didn't explain things very well. I'm still trying to refine what I want/need to do, but here are better details:
1. We need to display a Murdered on this Day slider on the home page. I have identified a strategy for filtering posts by <today's date> in any year. Presumably, this would do the trick for the slider, and I would use Publish date as the Murder date. But this strategy only works provided that these victim records end up as some kind of posts.
2. Clicking any victim record in the slider takes us to the details page, in a master/detail relationship.
Now it would be straightforward enough to just convert all of the database data into editable posts in WordPress - dropping the data into labeled renderings in these posts - but we lose the distinct database records if people are just changing things in the WP editor. So my thought of using BuddyPress to allow any Contributor to post a change, which is then reviewed and approved/rejected by the admins is in jeopardy if I need to also keep distinct table data intact, long-term. When someone logs in, uploads a new picture, updates the case notes with new info, I'm hoping to have these changes approved by an admin, then displayed in the details of each victim post.
Is this clearer? The challenge is to create posts, so I can take advantage of many of WordPress' revisioning and other content management perks, but retain a distinct database that remains up to date as users freshen records.
3. Alternately, I don't need the database tables updated all the time, if I have some kind of export process that could "rebuild" the data tables based on the updated posts. As long as we could get back to a distinct database at some point...
I'm a bit of a WordPress newb, but not helpless. I think I could possibly add custom fields for every database table field. As long as there is a 1:1 correspondence between all the db table fields and all the custom fields in WordPress. But is this sensible? Maybe 120 fields, per post? I'm new enough to WordPress, that I'm hoping an expert understands what my challenge is and can say, "Oh it's easy to do this if you XYZ." Or, "for heaven's sake DON'T XYZ or you will ABC".
Sorry for the vague question, but I'm just looking for some expert thoughts before I take a path.
Thank you
Bill
1. We need to display a Murdered on this Day slider on the home page. I have identified a strategy for filtering posts by <today's date> in any year. Presumably, this would do the trick for the slider, and I would use Publish date as the Murder date. But this strategy only works provided that these victim records end up as some kind of posts.
2. Clicking any victim record in the slider takes us to the details page, in a master/detail relationship.
Now it would be straightforward enough to just convert all of the database data into editable posts in WordPress - dropping the data into labeled renderings in these posts - but we lose the distinct database records if people are just changing things in the WP editor. So my thought of using BuddyPress to allow any Contributor to post a change, which is then reviewed and approved/rejected by the admins is in jeopardy if I need to also keep distinct table data intact, long-term. When someone logs in, uploads a new picture, updates the case notes with new info, I'm hoping to have these changes approved by an admin, then displayed in the details of each victim post.
Is this clearer? The challenge is to create posts, so I can take advantage of many of WordPress' revisioning and other content management perks, but retain a distinct database that remains up to date as users freshen records.
3. Alternately, I don't need the database tables updated all the time, if I have some kind of export process that could "rebuild" the data tables based on the updated posts. As long as we could get back to a distinct database at some point...
I'm a bit of a WordPress newb, but not helpless. I think I could possibly add custom fields for every database table field. As long as there is a 1:1 correspondence between all the db table fields and all the custom fields in WordPress. But is this sensible? Maybe 120 fields, per post? I'm new enough to WordPress, that I'm hoping an expert understands what my challenge is and can say, "Oh it's easy to do this if you XYZ." Or, "for heaven's sake DON'T XYZ or you will ABC".
Sorry for the vague question, but I'm just looking for some expert thoughts before I take a path.
Thank you
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.