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
LVL 1
billium99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason C. LevineNo oneCommented:
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.
0
billium99Author Commented:
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
0
Jason C. LevineNo oneCommented:
1. So add the murder date to the post/page/cpt as a custom field and use that to build the slider loop.

2. Master/detail in 2015 can represent a ton of different data structures.  In WordPress terms, you can either use your existing tables and the wpdb() class to query them or convert your data into custom fields and store it the wp_postmeta table.  Either works.  Your CRUD forms would either write to the original tables or the postmeta table. BuddyPress at this point in the discussion adds a ton of fluff that isn't really necessary.

3. Data are data.  No matter how you store it, you can export it.  If you are updating the original table structure, great.  If you store as postmeta, you bring stuff out as XML or JSON and then do what you need with it.

But is this sensible? Maybe 120 fields, per post?

Depends.  I would stick with the original table structure and just build a CRUD around it in WordPress...this is not particularly difficult, just time consuming.  You need to learn how to pass globals around and learn some WordPress specific functions, but if you have worked with PHP before, this will not come at steep learning curve price.  

Remember that WordPress is just PHP and MySQL with some pre-built functions and rules to follow.  It's not like there is one true way to tackle your tasks...you just need to pick a way and stick with it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WordPress

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.