By ccoletta on

Hello,

I'm the in-house web developer for a non-profit sailing center called Piers Park Sailing Center (www.PiersParkSailing.org). We have already used Drupal to roll out a kick ass 6.x site, and now we want to take it further.

I wanted to use Drupal to roll out an integrated website/database system that lets our business keep track of our member information (currently we use a big excel spreadsheet!). We also envisioned an online reservation system that users could go online and reserve a boat. The member should be able to log into Drupal and see their boat usage history.

I'm looking for some case studies for how other developers have kept track of database entities and recording how they interact. I was hoping the community could point me in the right direction, suggesting some good modules, and giving me a sense of what best practices are.

There are three database entities: "members", "boats", and "voyages", and all these entities interact with each other in an obvious way. When the member logs into their page, they should see all their contact information, as well as all the voyages they've made in which boat.

I haven't been developing with Drupal that long, but it's my goal to become a Drupal ninja, and just some general advice/tips/input on how best to proceed with this project would be really REALLY appreciated!

Looking forward to a fun and challenging project!
Chris

⋅ Categories: Drupal 6.x

Comments

peterx’s picture

peterx commented

Learn what a module does and how:
Creating modules - a tutorial: Drupal 6.x: http://drupal.org/node/206753

You will be in a better position to understand discussions about Drupal modules when you create your own.

petermoulding.com/web_architect

petermoulding.com/web_architect

socceronly’s picture

socceronly commented

What you want to do is similar to what I am trying to figure out with Drupal right now. That is connecting multiple entities and their relationships.

Are you starting from scratch/Drupal or do you have experience with database stuff? Do you know what a pivot table is? Because the last part of the fourth paragraph pretty much guarantees there will be more than three database entities.

peterx’s picture

peterx commented

The latest Drupal standard for table names is singular:
member
boat
voyage

A voyage could relate a member to a boat but you probably want more than one person on a boat.
member_voyage so that you can connect multiple members to a voyage.

Can a voyage involve more than one boat? voyage_boat.

Do your members learn how to sail? You might want a lesson table for "Learning how to sail", "Learning how to tie knots", "Learning how to brag about a slight ripple being 20 metre waves", so members can record their progress.

When you have a photograph gallery, you can connect voyages and members to photographs with additional tables.

The ability to write a module that creates a table gives you the option of interconnecting tables from different modules. You can then help Drupal modules breed.

petermoulding.com/web_architect

petermoulding.com/web_architect

socceronly’s picture

socceronly commented

So you are recommending learning module development. I am curious is that because it is a solution or the solution?

What I mean is can this kind of thing be done in Views or should one just do it in PHP/MySQL in a Drupal way?

members
voyages

member_voyages

I think you can use Views to look up stuff in member_voyages based on the NID of the member or voyage you happen to be looking at on the current page.

I just can't make any sense of it.

peterx’s picture

peterx commented

If you are a HTML/CSS type person then you could create everything using CCK and list it using Views. People who already know PHP and databases can learn more by following the module creation tutorials. You then have the knowledge to choose between a canned approach and rolling your own module. You can choose to write your own, or to take existing modules and stitch them together. I do not know of a module that does exactly what you want. If it were my project, I might start by making all members users and making the members table an extension to the users table. You can create a module that adds on to existing tables.

Read a few of the tutorials. Try some experiments on a test site. You will see how things connect together.

petermoulding.com/web_architect

petermoulding.com/web_architect

ccoletta’s picture

ccoletta commented

Some great feedback!

But I just want to take a step back and ask some higher level questions regarding what the best practices are for doing a relational database project like this specifically in Drupal.

Given ANY project with N number of entities, if I were going to do this project without Drupal, I know exactly how I'd do it. Make the tables in mySQL for each entity with all the attributes. White some PHP that will let a staff member pull up a list of the entities, all the boats, members, etc. For the sake of simplicity of example and assuming that there's one member per voyage per boat, in the voyage relational table I'd have a column for the member, and a column for the boat used.

But NOW add drupal to the mix. How do you define an entity in the Drupal sense? Do you use CCK? Create a node type called "member"? voyage? How does one relate one content type to another?

Or do I just proceed like I normally would proceed without drupal, and make a module(s) that interact with the databases I've set up? Would I create a "voyage portal" widget module that I could include onto the user's profile, which would run the SQL query to pull up all the user's voyages?

And another thing --- this concept of the relationship between Drupal "user" and sailing club "member" ... are they one and the same? Can I piggyback all the member functionality onto the user entity, including a place for staff members to keep administrative data on a member? Theoretically, there is a one-to-one relationship between member and user so they theoretically should be the same database entity.

The reservation issue: Is there a 6.x Drupal reservation module that people recommend? a reservation widget is basically just an event calendar, where the event is a voyage. Obviously the voyage could have any number of members and non-members - you could abstract that out into a generalized "passenger" entity. The voyage entity could relate to another table with all the passengers on that particular voyage - call it a "crew compliment" table". And the voyage entity would have a time, and boat, and a crew compliment, and a destination, and a time departed, and a time returned, etc. etc etc.

All this is simple and intuitive to do outside of Drupal. But what are the best practices for doing an application like this inside of Drupal? What kind of existing drupal functionality/contributed modules can I leverage here?

Thank you so much for your input!!!
-Chris