Re: [chiPHPug-discuss] Data Models

[<an...@gm...>]

Yes I have one....

I worked on a very large system that had a wonderful design for some of their tables that is worth investigating.  Perhaps this works best for Oracle but...

They make a table let's call it element.
They make the table with 3000 columns let us say.

The first 100 are system type fields like system id, unique id, parent id, branch id, version, expired, expire strategy, links, last user, creator, audit info, type and subtype, etc...  The next 900 are common properties that are relatively applicable to every element type such as cropping, origin, zoom, dpi, color schema, etc.

Now comes the fun...  The next 1000 are split into 500 and 500.  The first 500 are fixed to the type and the descriptive name comes from a table.  The next 500 are fixed to the subtype.  So in this system an element could be an image, an article, a custom object etc.  The subtype could maybe be jpg or tiff for an image type.  The last 1000 objects were custom user fields that could be broken out by say first third site, 2nd third user role, third user.

The key here is that null fields take no extra space really and having extra room makes it easier to extend without breaking your current schema.

The other fascinating aspect to the mentioned system is that all access to objects was done via an API no direct db calls.  So if you retrieved or saved an object the system took care of filling in defaults, audit history and version control.  Objects were only ever marked for delete and deleted during nightly maintenance which made the system extremely fast (no random writes at all) every update was an insert with a new revision number.

Anyhow thought I'd share this brilliant schema that was under development since before Oracle was born in Denmark.

Alec.

Now 
Sent via BlackBerry from T-Mobile

-----Original Message-----
From: junkmail <jun...@2z...>
Date: Tue, 20 Apr 2010 13:28:30 
To: Chicago PHP User Group<chi...@li...>
Subject: Re: [chiPHPug-discuss] Data Models

Thanks for the great tips Arlo & Matt!

I've thought about the class based idea for a little while now, but I 
was hoping maybe there was something else. I guess a tried and true 
design pattern exist for a reason, right?

I think I'm probably doing to end up with a hybrid model consisting of a 
class-based data model with hard coded sql for sticky stuff, and a 
moderately complex CRUD sql generator to handle basic joins across a few 
tables.

My end goal is to have a relational database schema / ui that can be 
user modifiable. Each domain will have hard coded operations that are 
domain specific, while allowing the user to create additional 
fields/entities for their liking. That is where the dynamic SQL comes 
into play, but for the domain specific stuff the class based model makes 
more sense.

Any more thoughts on the subject?

------------------------------------------------------------------------------
_______________________________________________
chiPHPug-discuss mailing list
chi...@li...
https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss