I have been hired by a client to build an intranet RDBMS for them to keep better track of their client information and appointments. I want to use Drupal 7 as opposed to a custom PHP/MySQL setup, as it has so many pre-buit functions that cut down development time. I am torn between the best way of going about this. I know to use Views in any case, but should I create custom content types, use Webforms, or a combination of both? I ask this because in pure database terms, the data tables will need to have a relational reference to one another (i.e. foreign keys). I will also need to be able to use specific data, such as a customer name, to be able to populate a form that my client can print out or save as an external document. If anybody has experience with this, your thoughts and suggestions are greatly appreciated. I will also try to get relational references up as the client makes them available.
Comments
What you are describing
What you are describing sounds closer to a CRM than it does an RDBMS. If this is indeed the case, it may be worth surfing the drupalnets for pre-existing CRM solutions that will allow you to hit the ground running.
At first glance, it seems like the Red Hen project ( http://drupal.org/project/redhen ) might suit your needs, at least to a degree...
Otherwise, if you absolutely must start from scratch, I'd recommend starting out by mapping out your entities and their relationships to one another. I suspect that the Entity/Node structure will be robust enough to handle most(if not all) of your needs for data storage (probably for input as well). The Relation and Entity Reference modules will help your entities to connect to one another in the ways you need them to as well.
Views can take arguments from the URL which will help you, if I understand you correctly, get reports with desired data very easily, provided you've got your view setup correctly.
If you can help it, I'd stay away from the DB layer as much as possible. Drupal is built in a way that abstracts SQL implmentation away from the user while optimizing its own database management.
When you break outside of that, you lose all of the benefits that working with Entities provides (views integration, for example) -- you can write (a bunch) more code to reclaim those benefits, but it will take you some serious time to get that code implemented and test it properly. Personally, the only reason I ever consider going to the DB directly is if Entity does not provide the functionality, speed, or structure that I need to complete a task (this is a pretty rare case).
Hi Timon, Thanks for the
Hi Timon,
Thanks for the info. Greatly appreciated. It sounds like the best overall way short of creating a custom module is to go with content types. Do you know of any way to import an Excel or CSV into a node? I know there is the Feeds module, but it's in alpha and I'm not very keen on using it on a paid project.
fgetcsv
PHP has the tools built in to handle csv imports. For instance fgetcsv(), which you can read about here: http://php.net/manual/en/function.fgetcsv.php can be used to pull lines of a csv, one at a time, into an array - which you can then use to populate node data in the backend.
PhpExcel http://phpexcel.codeplex.com/ is a nice little library which will allow you to pull in Excel files, specifically. It is more work to get this running, but nice if you don't happen to have access to .csv files converting Excel files to .csv is out of the question.
I've written an fgetcsv
I've written an fgetcsv routine for a custom PHP/MySQL RDBMS before, so I'm very familiar with it. Just wanted to see if there was something out there already set up for Drupal, as I know that writing a query for placing the information into a node can be pretty complex.
use a drush script instead of custom queries
let drush do all the bootstrap/fields/tables work for you, so you can focus on the tough stuff.
btw, you might want to give the feeds module a try even though it's alpha. It may work well enough for what you need.
RedHen and Migrate 2.x
As one of the maintainers for RedHen CRM, I think that it should meet your needs with a bit of additional config.
We've had great luck importing data into RedHen's schema using our own migration classes with the Migrate 2.x module. Soon we'll be polishing up those classes and releasing them as a contributed project (though for right now, they aren't abstracted enough to share).