Database Project

Hey,

I’m starting work on a new project that’s basically a massive search able database. It’s likely going to have over 5,000 rows (pages) of content with about 100 elements (that need to be plugged into the pages).

I know the evolution branch is limited to 5,000 documents so I figure I will keep this database independent (own table or db), but the problem is I need this DB to function as if it was a part of the main site so to speak.

What I need to work:
I need the ability to have each row (page) approved (thinking default is unpublished and then it’s validated and approved (published))
I need to have aliases for each page so they appear to be standard pages (domain.com/items/item869 or items/empire_building)

The database needs to be front end managed so:
Anyone can submit a new record (defaults to unpublished)
Anyone can submit additional information for a record (need a method of storing these changes unpublished so they can be verified)
(Current record’s information needs to be pulled and put into the new record form (well update form, but same information as new record) so they can see and correct existing information and/or just add other fields that are missing)
Anyone can search the site and this database (same search for both or independent?)

Approved users need to be able to:
See a list of unapproved record
See a list of records based on date of last approval/check (information needs to be re verified every say 1year )

They need to be able to edit/update records in any status (unpublished, published, needing re verification)

There shouldn’t be a need to delete records, but that should also be an option.


I think I have enough fields to cover everything such as the page alias, page title, creation date, approval date, last modified date, approved field. I just don’t know if there is a reliable option I can use to make all the above items work and protect the database from attack (inserted code).

I know there is eform2db and I got that working on a site once, but it seamed unreliable and would have DB errors when people tried to submit some information like name or email address. That and it’s not very well documented on how to get that working which isn’t a huge help to me.

Anyways I hope I explained things well enough that someone can give me some recommendations, examples, etc. on how I can pull this off! I would love to have this working by the 5th, but I don’t think that’s going to be a reasonable time line without help.

Thank,
AMDbuilder

(I can’t really discuss the data involved with this project outside of saying this will be sort of a craigs list type site of sorts...)

What is it that you would like modx to do? Is it just templating? Do you have a huge budget to create custom snippets / modules?

There’s a bunch of ways to do it if you need to be creative / on a tight budget.

You could let each one be a document, and use Quid to save the info in a seperate table to help front-end stuff.

I wouldnt use eform2db by itself. FDM is a good idea for frontend editing. I’ve actually used eForm with the docApi class to make a simpler FDM. But FDM has a lot of work put into it already.

Basically if you’re using modx only for user management, there’s gotta be an easier system that’s made for this type of CRUD. Maybe codeigniter?

Quid- http://modxcms.com/forums/index.php/topic,31666.0.html

edit - i meant Quid

Hello,

I’m wanting the entire site to be modx powered as it’s a familiar product and I know how to work with templates and such. I have no budget for this project at least at this point in time, but down the road we are hoping to pull in additional support and funding.

The main concern is this needs to scale and will likely have over 5,000 items (documents) not counting the pages for the website and db management. I seam to recall someone else built a similar site (website with large DB) that focused on bridges, but I can’t find it off hand.

Oh, the management can be frontend or backend.

AMDbuilder

1) how fast will it grow to 5K+ documents? If it will start at this size then you may be in trouble, but if it will grow to become this big in x amount of time, maybe you could start with Evo and then migrate to Revo as it grows.

2) Use an isolated DB for the ’100 elements’ but use modx documents do display/house the elements (this will give you the familiar modx structure and the SEO urls). This is only an option depending on the answer to 1

3) bite the bullet and risk using Revo from the start (make it a test/showcase project). But you will be lonely since there may not be as much assistance with Revo (but I notice that those few who have Revo experience are very attentive).

Hello,

I don’t have a firm number at this point in time, but I suspect within 2-3 months of launch there will be around 1,000 pages as we will be entering a lot of existing information into the system. One thing that will help is each state will be it’s own isolated site and have it’s own DB so that should keep the overall numbers lower. I will try to get some better figures on the document count, but at this point in time I am thinking more worse case. As for load times I don’t care if the DB content loads instantly. The main site pages should load rapidly, but the DB can have more of a delay if that helps (<5sec)

I’m going to go ahead and say the DB will be different than the CMS DB as the DB will stay on the local server. Depending on how the DB handles the load the content DB will be migrated to it’s own VPS or Dedicated server(s). (again thinking longer term... yea I know get it started then think long term...)

I’m not fixed on Evo or Revo at this point in time it’s more a question of where I can get help in starting this project.

Thanks again,
AMDbuilder

Hey,

I talked with the data person and have some more information regarding things:

The initial launch will only be FL and contain about 500-800 items. They expect to see an avg of about 2-3,000 record per state.

It will be about 4-8 months before the other states start adding information so the hope is revo will be launched by then and each state will be their own context. I don’t know if each context will share a common DB or separate DB. I guess this will be decided once revo is launched and people have a chance to start discussing multi-site/domain installs.

As noted earlier each state will have it’s own DB with the records so an example would be two DB for FL (CMS and Data)

On the database front things are looking to be a little more complex and if anyone has any suggestions that would be great!
There needs to be a buffer or validation system for the information. My thoughts are two tables and have the same ID in each table for that record. The one table contains a copy of the second table, but that copy can be updated by visitors. A records keeper checks the records that have been modified from master copy and verifies the changes thus updating the master copy.

The curveball, is there any way to output the data from the primary table and then use the secondary table to fill any missing fields?

I’m likely doing a bad job of explaining most of this, but still any help that anyone can offer on how to do this with modx would be great!

Thanks
AMDbuilder

I would make each state its own modx install/database and make use of this: http://wiki.modxcms.com/index.php/Access_another_database_from_MODx
Then I would just use the newsmanager/editor default unpublished...
Id set up one modx install perfect and try replicate it with bakckupmodx module...

www.domain.net/wa/ or
wa.domain.net/

Cool project!

Quote from: AMDbuilder at Dec 30, 2008, 11:56 PM

The initial launch will only be FL and contain about 500-800 items. They expect to see an avg of about 2-3,000 record per state.

1000 documents is no sweat for MODx on a decent server (one with enough processor power to parse the cache files on every page load). I’ve had at least three MODx sites over 3,000 documents, and two of these were using MODx 0.9.2 (one of which was over 5,000 documents and received a massive amount of traffic - >30 GB/month). If you can offload some of your data to linked tables that aren’t stored in the cache files, that would certainly be a good idea, but personally I wouldn’t worry too much about the cache bottleneck in practical terms (especially since Revo is just over the horizon should you need it).

My thoughts are that modx is only managing say 20 pages and it’s those pages that display the information from the DB, Update the DB, and handle DB interactions in general.

The trick is I don’t know how to accomplish this (if it can be done) or a reliable system for handling the DB interactions.

AMDbuilder

You’re wanting to recreate the CRUD for the tables. Why not let modx handle the crud?

1. Let every row be a modx document.
2. Use Quim to save each row into an external table so frontend lookup is fast without the joins, etc for TVs
3. Create a snippet to display the row needed. and you’re done
4. You can use all the extra cash/time you’ve saved to work on the 5000 limit problems if it’s needed.

In this scenario, the only custom item needed it the snippet to display results. Which is just an SQL query for one row and output.