How to handle relationships

michellem has asked for the wisdom of the Perl Monks concerning the following question:

I need some sage advice. I have built a generic web database system. It now can handle relationships, but it's a major cludge, in my opinion. I have a number of issues to deal with here - user interface - how to make relationships easy to create with table structure, add, and just see and - focus on SQL or focus on function? My cludge, instead of focusing on the SQL (creating foreign keys, creating the WHERE clauses on the fly) have actually created the functions of the relationships in perl, which works, but I have a nagging suspicion that it's not the best way to do it.

What I've done is the following:

1) User tells system which is primary key for that table, and which are foreign keys - system puts that info in a system table

2) For input - the user can specify lookups for menus and the like from the related tables

3) for output - an "output" lookup is done by the system based on the foreign key info placed in the system table.

4) The user interface for the relationships is not done, so right now, users cannot edit that information in a way that makes any sense to them (it makes sense to me, but that's not the point).

5) Relational integrity is not implemented in any way, which to my mind is a *bad thing*. I certainly could implement relational integrity again using perl instead of SQL.

One major problem I have is that if I focus on using SQL, it means that I have to radically beef up my table creation tool, and also somehow figure out how to have a user interface that makes sense to the non-SQL-speaking unwashed masses :-).

So, any advice, comments, etc. are welcome.

Comment on How to handle relationships

Replies are listed 'Best First'.
Re: How to handle relationships by tadman (Prior) on Aug 26, 2002 at 08:46 UTC
There's a lot of layers to this problem that could be difficult to sort through. For example, making tables. If you have the time, it would be nice to see an interface that can disintermediate between low-level SQL and relatively high-level Perl. I mean, there's a whole bunch of ways to make and alter tables, why not standardize the interface? `my $table = SQL::Table->new(name => "client"); push(@{$table->columns}, SQL::Column->new(name => "id", type => { char + => 50 }); $table->commit();` [download] The thing that really irks me is that, when developing an application, no matter how "standard" the SQL you use is, it's always a huge hassle to move from one back-end to another. If there were a sort of "driver" that wrote working SQL for you, it would be a simple matter of dropping a new one in. Mostly. Of course, nothing is perfect.	[reply] [d/l]
Re: How to handle relationships by Tomte (Priest) on Aug 26, 2002 at 10:38 UTC
I did something similar (though not in perl), my solution was to seperate the attributes of "things" into a table, each attribute a row in this table with a key pointing to the "thing" they belong to, because this way all tables of a certain type look the same and the layer provided for the coder can treat thes in a generic way Basically: `table allowed_types \| \| table types_allowed_for_<thingname>_named \| \| \| table <thing_name>_attributes-------table <thingname>_things` [download] with a layer of two factories ("ThingFactory" and "ThingAttributeFactory" and generic Implementations of "Thing" and "ThingAttribute" and a way to select by value; not even the coder using this needs to know any SQL). There are a few admisnistrative tables left out of the diagramm. It kind of works, the generated sql is hard to read but reasonable fast for an approach like this. I hope this makes any sense to you, if not, forgive me; I struggled a lot for this "Not relational but possible in a RDBMS-thingie", and the approach of attribute-tables saved my day regards, tomte	[reply] [d/l]