Thread

We want to use the MSM for six sites, all of which will be fed by two large databases with several tables in each. Users will be able to search at each site and download database content. Members will have unrestricted access to the content, others will pay in advance and authorise a deduction prior to download. Content will be screen rendered and/or available in an Excel file. 

It has been suggested we consider pulling the content from databases into EE and using tags for display and search or using queries to pull the content into EE templates or keeping the databases separate and linking to them. We are unsure of the way to go.

We would like to hear from anyone with a similar set-up or who uses at least two significant databases.

ghappy, your question is a little bit confusing.  I’m not sure what exactly you are trying to do via the two databases.  Where is this data coming from?  Is it in the form of a MySQL database?  Is it that you want to be able to display the data in the pages via HTML tables to the visitors based on their account status?  Are you asking about importing the data into the same database as EE so you can then grab it using the query module?  I think anyone is going to need more details to have any idea of how to answer you.

Jamie

Thanks Jamie, hope the following clarifies:

Where is this data coming from?
The data is currently contained in two excel files, each with three or four worksheets. Within each file the worksheets are inter-related via a common ID.
 
Is it in the form of a MySQL database?
No, not at this point.

Is it that you want to be able to display the data in the pages via HTML tables to the visitors based on their account status?
Essentially yes. The data in one form or another is of benefit to users. It will be searchable at each site and most of it available via screen display free of charge to anyone. Some of it will only be accessible (via screen display and/or in a downloadable file) to members who have registered, paid an annual sub and are logged in. That same data will also be available to a second category of users, who having registered and established a credit balance will authorize a deduction from their balance prior to download.


Are you asking about importing the data into the same database as EE so you can then grab it using the query module?
Not necessarily. We are asking how best to integrate/manage these two large databases given what we want to do with the data and given that both databases will be constantly maintained.

OK, I’ve done some of this kind of thing before.

I would, in the case, recommend putting the data into SQL form.  That way searching, displaying, etc. could all be done much more efficiently.  Where the difficulty would come in is managing it.  It sounds like there’s relationships between the data in a variety of ways and it sounds like you need an interface to then allow you to manage that data.

How you’d approach that within EE would have a lot to do with how those relationships work AND/OR how much traffic this data will create.  Is this a niche area where you probably won’t ever have large numbers of visitors hitting the site and the data being pulled or would it be something you’d need a high level of performance?  It also kind of depends on what you mean by large database.  As that doesn’t really give me an idea of number of entries.

It may be that the EE weblogs/channels (as they are now called) will be all the functionality you need.  You can already create relationships between section channel entries within EE’s core functionality.  And of course you can have a field for each column of information.  Or using the Matrix extension by Pixel&Tonic;you could have the columns in slightly slicker setup.

But there’s drawbacks to using channels with large data sets.  They assume some things about how searching/sorting will work.  And they are pretty heavy on the SQL server on the display end especially if you start sorting on any custom fields.  More troublesome when you want to do some pretty bespoke stuff on the user end the data is all stored on a separate table from other entry stuff like date and title.

The way to get around that is to build your own interface to a custom table using EE’s module setup.  I’ve done this in a few cases.  But the onus is then on you and your developers.  You need to create your own CP interface to that data and your own tags to then pull the data out.  The plus side is that you can tailor the SQL tables to exactly what you need and want both in terms of performance and in terms of the types of data you are storing. 

The investment in time and money will be higher with this second option and obviously you’d need someone who knows how to do all of that.  I could see both approaches working but the right choice is dependent on your needs and means.

Jamie

Jamie, many thanks for quick response.

It sounds like there’s relationships between the data in a variety of ways
YES

and it sounds like you need an interface to then allow you to manage that data.
YES, vital

Performance?
Based on limited experience, we expect about 10,000 visitors a day, most of them searching on and accessing data as anonymous users.

It also kind of depends on what you mean by large database.
Database One has 20,000 rows and 25 columns in Worksheet 1, 40,000 rows and 5 columns in Worksheet 2 and 30,000 rows and 10 columns in Worksheet 3.  Database Two is the same configuration just a bit larger.

Am I right in thinking that we would firstly import the databases into MySQL and then use EE’s module setup to build the interface? If so, are you able to point me in the direction of further reading I can do on this and bearing in mind this is a six site installation, is there any advantage in waiting for EE 2.