Page 1 of 1
OOP with a relational database
Posted: Thu Sep 08, 2005 1:55 pm
by deltawing
I'm at the design process for a website which is becoming quite a large project. I'm fairly new to the idea of relational database design, but I've constructed the tables (I was expecting to need three or four, and I have 13) and now I'm trying to lay out my class structure. Logically, I have one class for all the "major" tables, which works fine, but the problem comes with accessing the intermediate tables created for many-to-many relationships.
For example, I have one table called "gigs". This lists the GigID (PK), date and time, and the VenueID, as listed in the "venues" table. Representing the venue of a gig doesn't need to be any more complex, because each gig can't have more than one venue (one-to-many). The problem comes with artists. I have an "artists" table, and an intermediate table called "gigartists", which has only two columns "GigID" and "ArtistID". This allows for the many-to-many relationship of one artist possibly having many gigs, and one gig possibly having multiple artists.
Now, I have an Gig class, a Venue class and an Artist class. How should I go about accessing the gigartist table? Should I extend the Gig class and the Artist class (very un-OO), or should I make a GigArtist class, or is there something I haven't thought of? I'm sure this is a fairly common problem, so surely there must be a common solution.
Posted: Thu Sep 08, 2005 2:11 pm
by Christopher
First, I would rethink having one class for each "major" table. Sometimes there is a one-to-one relationship between tables and classes. But you should focus first on the classes you actually need and then have them deal with the database through mapper or gateway classes. Your database and application design are related but not identical. Perhaps you could try TDD to help you reveal what classes you really need.
Posted: Thu Sep 08, 2005 2:58 pm
by deltawing
One class for each major table just seemed to me to be the logical way to do it. It makes sense to just write new Gig(), new Artist()... etc. And it just looks like quite a simple layout to have the class structure almost mirror the database structure. I'm having trouble invisioning anything else.
I've never tried TDD before, but it looks scary. I wouldn't know how to go about it. If I do end up going about it the way you suggest, I'm going to have to get a lot of help from somewhere.
Posted: Thu Sep 08, 2005 4:07 pm
by feyd
there are several members here that can help guide you down the path of TDD. But as for the table-class structuring, a manager for each logical component, with delegates is likely a path I'd personally head. As usual, you'd still run everything through a database abstraction layer, but basically, what I see is a class for each non-linking table, and a seperate class for each of the linking situations. This linking class would probably either take instance references to each of it's associated table classes and become their manager or something.. Alternately, a superclass that understands each table and the linking could also work, but would take more work to set up correctly (and test) than more specific classes.
Posted: Thu Sep 08, 2005 4:26 pm
by deltawing
right, thanks. For the moment I'll continue trying to get my head around this, probably with your first suggestion. And as a side project, I'll look into TDD, and if it seems I'll be able to develop that way, I'll start again with that. Methinks this gives me an excuse for another post

Posted: Thu Sep 08, 2005 4:38 pm
by McGruff
Martin Fowler's Patterns of Enterprise Architecture has a lot of material on object-relational patterns. It's one of those must-have books (you can see a brief summary of the patterns
here).
Posted: Thu Sep 08, 2005 8:47 pm
by Christopher
deltawing wrote:One class for each major table just seemed to me to be the logical way to do it. It makes sense to just write new Gig(), new Artist()... etc. And it just looks like quite a simple layout to have the class structure almost mirror the database structure. I'm having trouble invisioning anything else.
If you are having trouble envisioning it, perhaps you should describe how users use the system and we can provide some ideas. Remember, don't describe the system, tell us how users move though it.
Posted: Fri Sep 09, 2005 7:37 am
by deltawing
Ok. When a user goes to the home page, they see upcoming gigs, and if their details are saved to a cookie, they see gigs in their area. Users can search for gigs based on location, date, time, cost, artist, etc. Anyone can add a gig, but you can only edit a gig if you added it (based on login), you are the artist, or you own the venue.
Is that what you wanted me to describe?
Posted: Sat Sep 10, 2005 2:04 am
by Christopher
deltawing wrote:Ok. When a user goes to the home page, they see upcoming gigs, and if their details are saved to a cookie, they see gigs in their area. Users can search for gigs based on location, date, time, cost, artist, etc. Anyone can add a gig, but you can only edit a gig if you added it (based on login), you are the artist, or you own the venue.
Is that what you wanted me to describe?
Yeah, that is it. In your first post you mention Artists and Venues. At some point you will need to make a decision how you want to handle those. You may think you need a separate table, but you could also do a DISTINCT query on you Gigs table and generate a select from that. You'll need to make that call at some point.
From your description you need have Gigs and User Accounts. You also note that you need some Access Control so that only certain users can edit Gigs. The question is whether you want to start with Gigs or User Accounts? I'd say start with Gigs. I would start with a Table Data Gateway type class for Gigs. Creae the minimumn you need to fetch a single record and mutiple records based on you "upcoming gigs" criteria.
Then (some would say first) install SimpleTest and create some very basic tests for the Gigs class and some simple web test for displaying gigs. That will give you a feel for how solid your code is.
From there I would implement the User Accounts and following that tackle the Access controls. Rather than creating 13 tables, proceed by only creating what you need.
Posted: Wed Sep 21, 2005 6:49 pm
by alvinphp
One of the first things I do (after requirements gathering) is create an ERD. That then becomes the basis of my class structure where the major tables tend to become classes. I think it is a bad idea to creat a super class that has methods for many major tables, it defeats one of the purposes of OOP.
How should I go about accessing the gigartist table? Should I extend the Gig class and the Artist class (very un-OO), or should I make a GigArtist class, or is there something I haven't thought of?
IMHO, It depends on how your presentation looks. My first thought would be you put the gigartist methods in the Gig class as I would think the artist list would be a little more static so you probably would have a Gig page where you create the gig then add artitsts from an existing list of artists. If an artist was missing you would first add the artist then go to your gig page and add the artist to the gig.