Menu
▾
▴
chiphpug-discuss
|
From: junkmail <jun...@2z...> - 2010-04-15 22:38:34
|
I am curious what people use for fetching data in PHP. I've been calling this my model. Over the past few years I've slowly built up a framework that encompassed basic mysql_connect() and mysql_query() wrapping for error handling and simplicity. For the next step I made a basic query generator for 1-table CRUD operations using simple table definitions in associative arrays. The next step is for complex CRUD with table joins. This is the hard part that I'm not 100% sure how I should tackle it. I'm thinking it will start small, but ultimately I'll move to full-blown relationship modeling with the ability to perform CRUD operations across any relationship. My goal is to write little-to-NO hardcoded SQL into my PHP. I'm curious to hear about any strategies on this problem, or any insights that may have been encountered in similar endeavors. Thanks! |
|
From: matt d. <mm_...@ya...> - 2010-04-16 12:33:08
|
I'm not sure 'no SQL' is all that desirable once you get past a single join.
I prefer to build the query in MySQL query browser and after I have what I want use something simple like
getQueryRows($q)
If you where worried about clean data just run all $_REQUEST vars through a scrub globally.
$_REQUEST = clean($_REQUEST);
What's really helpful is if all your classes extend a basic representation of the table row as a class.
The part where you say "table definitions in associative arrays" would be automatically handled there using "DESCRIBE $this->table" and populating $this->cols. Unless you are already doing that?
You would also have things like:
$foo = new foo(insert_row());
$foo->save($array); // uses set_row() and the built in table definition with validation
or
$foo2 = $foo->clone($override_data_array); // creates a new row overriding any column data with the passed array, Each class can define pre_clone
child call
$name = $foo->name(); // any column using "function __call($nm,$argv)" or overridden by a function and returns null if it doesn't exist
formatting
$foo_created = $foo>readableDate("created_date");
json
$foo_json = $foo->json();
forms
$html_form = $foo->getEditor();
Then you can have something like this:
$users = getQueryObjects("select * from users where company_id = 1","user");
or
$report = new reportTable($q,$reportclass,$displaycols);
$report->display(); // sortable rows of data
________________________________
From: junkmail <jun...@2z...>
To: Chicago PHP User Group <chi...@li...>
Sent: Thu, April 15, 2010 5:19:47 PM
Subject: [chiPHPug-discuss] Data Models
I am curious what people use for fetching data in PHP. I've been calling
this my model.
Over the past few years I've slowly built up a framework that
encompassed basic mysql_connect() and mysql_query() wrapping for error
handling and simplicity.
For the next step I made a basic query generator for 1-table CRUD
operations using simple table definitions in associative arrays.
The next step is for complex CRUD with table joins. This is the hard
part that I'm not 100% sure how I should tackle it. I'm thinking it will
start small, but ultimately I'll move to full-blown relationship
modeling with the ability to perform CRUD operations across any
relationship.
My goal is to write little-to-NO hardcoded SQL into my PHP.
I'm curious to hear about any strategies on this problem, or any
insights that may have been encountered in similar endeavors.
Thanks!
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
chiPHPug-discuss mailing list
chi...@li...
https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss
|
|
From: Arlo L. <ar...@ar...> - 2010-04-19 23:57:59
|
Hello, > I am curious what people use for fetching data in PHP. I've been calling > this my model. > My goal is to write little-to-NO hardcoded SQL into my PHP. > > I'm curious to hear about any strategies on this problem, or any > insights that may have been encountered in similar endeavors. My experience sounds a lot like yours. I've always used a custom library file that handles SQL queries, adding error logging options and escaping and smoothing out some of the wrinkles in PHP's MySQL functions. Then a year ago I started using OOP for all but the simplest projects. That served to further abstract the data model from my code, and now I never find myself needing any SQL outside of my classes. Here are some notes about how I've ended up building my classes: - I typically have one class for each significant database table. For example, I might have a "user" table, with a related "user_status" and "user_location" table, but the User class would encompass all three. - The User class would contain a getRecords method, which takes a list of IDs and returns an array of data about the requested users. For relational data like user_status, my convention has been to return the ID value as $status, but the text value from the user_status table as $status_display. Then the code calling the method can use whichever value is most convenient. I can call this method directly if I know the IDs of the records I want to get. - The class also contains a getUsers method, which takes an associative array of search criteria, from which it builds a more complex query to pass into getRecords. The search criteria are specific to each class and might include something like $login_enabled. The nice thing here is that the definition of "login_enabled" can be fairly complex and encompass more than one database field, but my code doesn't have to know about that -- it's only defined in this one spot. Also, this method constructs the query based on what criteria are used, so it just joins the tables it needs. I use this method when I don't know the IDs but I want to get a particular set of records. - And I have a Search class that passes requests through to the other classes but adds pagination functionality. This is a separate class because this functionality is the same for all classes, so it would be redundant to build it into each class. - The classes also contain saveToDB methods, but this is very straightforward because the data I save is typically in a one-to-one relationship with the fields they save into. For example, the $status value just saves to the status field, and I don't have to worry about the related user_status table. If I need to set something like "login_enabled" which might involve changing more than one field, I would add a specific method for that. - And some classes have additional "convenience" methods for common tasks. For example, if I often want to get a user's status and don't care about the other data, I'll make a getUserStatus method with a simple SQL query that bypasses those all-purpose methods. I hope that makes sense / helps! Cheers, -Arlo _______________________________ Arlo Leach 773.769.6106 http://arlomedia.com The killer app for the iPad http://washboardapp.com |
|
From: junkmail <jun...@2z...> - 2010-04-20 18:34:47
|
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? |
|
From: <an...@gm...> - 2010-04-20 18:59:12
|
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 |
|
From: Arlo L. <ar...@ar...> - 2010-04-20 19:48:56
|
Hello, > 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. Just one table for the whole project? That sounds so simple! In fact, it reminds me of the MacBook Wheel: http://www.youtube.com/watch?v=9BnLbv6QYcA (You were being sarcastic, 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. One more thought is that I've used third-party software where the SQL queries were constructed from so many little pieces that it was impossible to understand how the code integrated with the database. When you want to customize something like that, you end up getting frustrated and just wanting to know where a value comes from already! So in my classes most of the queries are "whole," or at least all the pieces are contained in the same method, so if anyone else needs to modify it they don't have to go on a scavenger hunt through the whole project to figure out how the queries are constructed. Cheers, -Arlo _______________________________ Arlo Leach 773.769.6106 http://arlomedia.com The killer app for the iPad http://washboardapp.com |
|
From: <an...@gm...> - 2010-04-20 21:19:26
|
I wasn't being sarcastic at all Arlo I was giving you a design pattern for working with very large customizeable and extensible data set, sorry if you can't wrap your head around this design. I gave the example of Elements table so obviously this is one table definition of Many. The table used partitioning to divide the table into multiple sets of data 7 in our case so only 1 set of data files would be modified in a single day easing the backups of a 7TB database, not to mention 2000 concurrent users in a single monolithic system. But the key is to group data as it would be likely to have similar sets of data and define your table schema from that approach. You could probably guess users were in a users table products were in a product table etc. The concepts involved here are quite advanced and basically turns Oracle/Mysql/Postgres into a system more like big tables. The key to the data model (which is also similar to big tables) is to abstract the query language from the storage model. The query API handles resolving column names into field numbers avoiding the scavenger hunt. Another type of DB layout was asked for and I believe I have answered that. Alec Sent via BlackBerry from T-Mobile -----Original Message----- From: Arlo Leach <ar...@ar...> Date: Tue, 20 Apr 2010 12:22:05 To: Chicago PHP User Group<chi...@li...> Subject: Re: [chiPHPug-discuss] Data Models Hello, > 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. Just one table for the whole project? That sounds so simple! In fact, it reminds me of the MacBook Wheel: http://www.youtube.com/watch?v=9BnLbv6QYcA (You were being sarcastic, 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. One more thought is that I've used third-party software where the SQL queries were constructed from so many little pieces that it was impossible to understand how the code integrated with the database. When you want to customize something like that, you end up getting frustrated and just wanting to know where a value comes from already! So in my classes most of the queries are "whole," or at least all the pieces are contained in the same method, so if anyone else needs to modify it they don't have to go on a scavenger hunt through the whole project to figure out how the queries are constructed. Cheers, -Arlo _______________________________ Arlo Leach 773.769.6106 http://arlomedia.com The killer app for the iPad http://washboardapp.com ------------------------------------------------------------------------------ _______________________________________________ chiPHPug-discuss mailing list chi...@li... https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss |
|
From: Arlo L. <ar...@ar...> - 2010-04-20 21:37:58
|
> I wasn't being sarcastic at all Arlo I was giving you a design pattern for working with very large customizeable and extensible data set, sorry if you can't wrap your head around this design. Well ... sure, I can wrap my head around it, but all the superlatives like "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, large numbers and the generality of your "elements" example made it sound like a joke ... my mistake then. The Onion video is still funny, though. Cheers, -Arlo _______________________________ Arlo Leach 773.769.6106 http://arlomedia.com The killer app for the iPad http://washboardapp.com |
|
From: Steve G. <sg...@wc...> - 2010-04-20 21:52:20
|
I'm a big fan of one table design. When I need a second table, I often create an entirely separate database for it, just to keep things separate. I don't like it when my tables "touch." Viruses spread easier when they do. Hi Arlo. Steve On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: >> I wasn't being sarcastic at all Arlo I was giving you a design pattern for >> working with very large customizeable and extensible data set, sorry if you >> can't wrap your head around this design. > > > Well ... sure, I can wrap my head around it, but all the superlatives like > "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, large > numbers and the generality of your "elements" example made it sound like a > joke ... my mistake then. The Onion video is still funny, though. > > Cheers, > -Arlo > > _______________________________ > > Arlo Leach > 773.769.6106 > http://arlomedia.com > > The killer app for the iPad > http://washboardapp.com > > > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss -- Steve Gadlin Web Development Manager Weigel Broadcasting 312.660.8375 sg...@wc... |
|
From: matt d. <mm_...@ya...> - 2010-04-21 00:10:24
|
I'm resisting the urge to respond to this... or the 3000 columns. ________________________________ From: Steve Gadlin <sg...@wc...> To: Chicago PHP User Group <chi...@li...> Sent: Tue, April 20, 2010 4:40:22 PM Subject: Re: [chiPHPug-discuss] Data Models I'm a big fan of one table design. When I need a second table, I often create an entirely separate database for it, just to keep things separate. I don't like it when my tables "touch." Viruses spread easier when they do. Hi Arlo. Steve On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: >> I wasn't being sarcastic at all Arlo I was giving you a design pattern for >> working with very large customizeable and extensible data set, sorry if you >> can't wrap your head around this design. > > > Well ... sure, I can wrap my head around it, but all the superlatives like > "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, large > numbers and the generality of your "elements" example made it sound like a > joke ... my mistake then. The Onion video is still funny, though. > > Cheers, > -Arlo > > _______________________________ > > Arlo Leach > 773.769.6106 > http://arlomedia.com > > The killer app for the iPad > http://washboardapp.com > > > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss -- Steve Gadlin Web Development Manager Weigel Broadcasting 312.660.8375 sg...@wc... ------------------------------------------------------------------------------ _______________________________________________ chiPHPug-discuss mailing list chi...@li... https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss |
|
From: Anacreo <an...@gm...> - 2010-04-21 01:40:32
|
Yeah I don't have time for this... I've received similar poo poo responses from this group on similar type discussions. I'll tell you I'm a performance EXPERT and have been designing large scale systems for over 15 years. The general arguments have always been that the approach is too simple or too not what you guys are doing. Try working on a large scale and you'll realize ruby on rails and PHP with training wheels isn't quite the only answer. As for arbitrary numbers its not so arbitrary your code should meet your demands. When I signed up I didn't realize this group was PHP for pea-pod size projects with a pea-pod brained Arlo. Alec On 4/20/10, matt donohue <mm_...@ya...> wrote: > I'm resisting the urge to respond to this... or the 3000 columns. > > > > ________________________________ > From: Steve Gadlin <sg...@wc...> > To: Chicago PHP User Group <chi...@li...> > Sent: Tue, April 20, 2010 4:40:22 PM > Subject: Re: [chiPHPug-discuss] Data Models > > I'm a big fan of one table design. When I need a second table, I often > create an entirely separate database for it, just to keep things separate. > I don't like it when my tables "touch." Viruses spread easier when they do. > > Hi Arlo. > > Steve > > > On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: > >>> I wasn't being sarcastic at all Arlo I was giving you a design pattern >>> for >>> working with very large customizeable and extensible data set, sorry if >>> you >>> can't wrap your head around this design. >> >> >> Well ... sure, I can wrap my head around it, but all the superlatives like >> "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, >> large >> numbers and the generality of your "elements" example made it sound like a >> joke ... my mistake then. The Onion video is still funny, though. >> >> Cheers, >> -Arlo >> >> _______________________________ >> >> Arlo Leach >> 773.769.6106 >> http://arlomedia.com >> >> The killer app for the iPad >> http://washboardapp.com >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > > -- > Steve Gadlin > Web Development Manager > Weigel Broadcasting > 312.660.8375 > sg...@wc... > > > > > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > -- Sent from my mobile device |
|
From: matt d. <mm_...@ya...> - 2010-04-21 03:26:37
|
I'm missing the simple in your explanation or the reality of 3000 columns (Seriously???). Do you project the schema on the side of buildings? ________________________________ From: Anacreo <an...@gm...> To: Chicago PHP User Group <chi...@li...> Sent: Tue, April 20, 2010 8:40:23 PM Subject: Re: [chiPHPug-discuss] Data Models Yeah I don't have time for this... I've received similar poo poo responses from this group on similar type discussions. I'll tell you I'm a performance EXPERT and have been designing large scale systems for over 15 years. The general arguments have always been that the approach is too simple or too not what you guys are doing. Try working on a large scale and you'll realize ruby on rails and PHP with training wheels isn't quite the only answer. As for arbitrary numbers its not so arbitrary your code should meet your demands. When I signed up I didn't realize this group was PHP for pea-pod size projects with a pea-pod brained Arlo. Alec On 4/20/10, matt donohue <mm_...@ya...> wrote: > I'm resisting the urge to respond to this... or the 3000 columns. > > > > ________________________________ > From: Steve Gadlin <sg...@wc...> > To: Chicago PHP User Group <chi...@li...> > Sent: Tue, April 20, 2010 4:40:22 PM > Subject: Re: [chiPHPug-discuss] Data Models > > I'm a big fan of one table design. When I need a second table, I often > create an entirely separate database for it, just to keep things separate. > I don't like it when my tables "touch." Viruses spread easier when they do. > > Hi Arlo. > > Steve > > > On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: > >>> I wasn't being sarcastic at all Arlo I was giving you a design pattern >>> for >>> working with very large customizeable and extensible data set, sorry if >>> you >>> can't wrap your head around this design. >> >> >> Well ... sure, I can wrap my head around it, but all the superlatives like >> "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, >> large >> numbers and the generality of your "elements" example made it sound like a >> joke ... my mistake then. The Onion video is still funny, though. >> >> Cheers, >> -Arlo >> >> _______________________________ >> >> Arlo Leach >> 773.769.6106 >> http://arlomedia.com >> >> The killer app for the iPad >> http://washboardapp.com >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > > -- > Steve Gadlin > Web Development Manager > Weigel Broadcasting > 312.660.8375 > sg...@wc... > > > > > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > -- Sent from my mobile device ------------------------------------------------------------------------------ _______________________________________________ chiPHPug-discuss mailing list chi...@li... https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss |
|
From: CM L. <cmc...@gm...> - 2010-04-21 04:29:00
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Mudslinging is pointless. What Anacreo proposes requires that the "schema" be handled by the script rather than the database (a more traditional schema). There's certainly cases where that could be useful, in the same way that there are cases where Google's file system makes sense. I think we can agree that it's not a common design and probably wouldn't be needed for small to medium scale projects. I do see large similarities with document oriented databases, but Anacreo's proposition has the added benefit of proper foreign keys (though replication/horizontal scaling is, of course, more difficult). In any event, it's just a different, specialized tool and I appreciate hearing the thought (though I don't appreciate the derisive comments Anacreo's made.) Back to junkmail's question about user-created fields: I've seen two options: First, the PHP serves more or less as a front for ALTER TABLE ADD COLUMN x commands (with checks for column name length, uniqueness, etc). Whenever such a column is added, an entry in a "field list" table is also inserted so that, whenever loading a model of that type, the extra fields are also grabbed. With proper caching this query-per-instance can be avoided almost all together. Second, a table serves as the "custom fields" table, with a column for associated model, a column for field name, and a column for value. This is used by Trac (ticket management software) for example, and either requires a left join for each additional field (obviously taxing), code that is smart enough to handle the duplicate data returned by a single left join (somewhat complex), or two separate queries (one for the model's base data and one for the custom field data). If you go the "custom fields" table route, I'd highly suggest you use multiple "value" columns, one for each type and/or foreign key (i.e. value_int, value_varchar, etc.), otherwise the "value" column has to be the most generic of the types you put in to it (often becoming a text field), which is highly inefficient, difficult to compare, etc. Best of Luck, CM Lubinski - --- http://cmlubinski.info matt donohue wrote: > I'm missing the simple in your explanation or the reality of 3000 columns (Seriously???). > Do you project the schema on the side of buildings? > > > > ________________________________ > From: Anacreo <an...@gm...> > To: Chicago PHP User Group <chi...@li...> > Sent: Tue, April 20, 2010 8:40:23 PM > Subject: Re: [chiPHPug-discuss] Data Models > > Yeah I don't have time for this... I've received similar poo poo > responses from this group on similar type discussions. > > I'll tell you I'm a performance EXPERT and have been designing large > scale systems for over 15 years. > > The general arguments have always been that the approach is too simple > or too not what you guys are doing. Try working on a large scale and > you'll realize ruby on rails and PHP with training wheels isn't quite > the only answer. > > As for arbitrary numbers its not so arbitrary your code should meet > your demands. When I signed up I didn't realize this group was PHP > for pea-pod size projects with a pea-pod brained Arlo. > > Alec > > On 4/20/10, matt donohue <mm_...@ya...> wrote: >> I'm resisting the urge to respond to this... or the 3000 columns. >> >> >> >> ________________________________ >> From: Steve Gadlin <sg...@wc...> >> To: Chicago PHP User Group <chi...@li...> >> Sent: Tue, April 20, 2010 4:40:22 PM >> Subject: Re: [chiPHPug-discuss] Data Models >> >> I'm a big fan of one table design. When I need a second table, I often >> create an entirely separate database for it, just to keep things separate. >> I don't like it when my tables "touch." Viruses spread easier when they do. >> >> Hi Arlo. >> >> Steve >> >> >> On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: >> >>>> I wasn't being sarcastic at all Arlo I was giving you a design pattern >>>> for >>>> working with very large customizeable and extensible data set, sorry if >>>> you >>>> can't wrap your head around this design. >>> >>> Well ... sure, I can wrap my head around it, but all the superlatives like >>> "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, >>> large >>> numbers and the generality of your "elements" example made it sound like a >>> joke ... my mistake then. The Onion video is still funny, though. >>> >>> Cheers, >>> -Arlo >>> >>> _______________________________ >>> >>> Arlo Leach >>> 773.769.6106 >>> http://arlomedia.com >>> >>> The killer app for the iPad >>> http://washboardapp.com >>> >>> >>> ------------------------------------------------------------------------------ >>> _______________________________________________ >>> chiPHPug-discuss mailing list >>> chi...@li... >>> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> -- >> Steve Gadlin >> Web Development Manager >> Weigel Broadcasting >> 312.660.8375 >> sg...@wc... >> >> >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLzn8Afzi1OiZiJLARAjiDAJ0Xxtg2xJOdN/sX7Nkp4wkRivRG3ACdE9H4 XE+5BWXG/aCmRsRUSLQFgnE= =WIIa -----END PGP SIGNATURE----- |
|
From: Jason R. <ja...@ho...> - 2010-04-21 05:18:24
|
Good comments. I forgot about the follow on question regarding user modified schemas.. Also I just found this because replying to this thread got me curious about other data persistence layer approaches. Dont know how I missed it before but it seems interesting: http://code.google.com/p/torpor-php/ http://paultomlinson.net/tech/torpor CM Lubinski wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Mudslinging is pointless. > > What Anacreo proposes requires that the "schema" be handled by the > script rather than the database (a more traditional schema). There's > certainly cases where that could be useful, in the same way that there > are cases where Google's file system makes sense. I think we can agree > that it's not a common design and probably wouldn't be needed for small > to medium scale projects. > > I do see large similarities with document oriented databases, but > Anacreo's proposition has the added benefit of proper foreign keys > (though replication/horizontal scaling is, of course, more difficult). > In any event, it's just a different, specialized tool and I appreciate > hearing the thought (though I don't appreciate the derisive comments > Anacreo's made.) > > Back to junkmail's question about user-created fields: > I've seen two options: > > First, the PHP serves more or less as a front for ALTER TABLE ADD COLUMN > x commands (with checks for column name length, uniqueness, etc). > Whenever such a column is added, an entry in a "field list" table is > also inserted so that, whenever loading a model of that type, the extra > fields are also grabbed. With proper caching this query-per-instance can > be avoided almost all together. > > Second, a table serves as the "custom fields" table, with a column for > associated model, a column for field name, and a column for value. This > is used by Trac (ticket management software) for example, and either > requires a left join for each additional field (obviously taxing), code > that is smart enough to handle the duplicate data returned by a single > left join (somewhat complex), or two separate queries (one for the > model's base data and one for the custom field data). If you go the > "custom fields" table route, I'd highly suggest you use multiple "value" > columns, one for each type and/or foreign key (i.e. value_int, > value_varchar, etc.), otherwise the "value" column has to be the most > generic of the types you put in to it (often becoming a text field), > which is highly inefficient, difficult to compare, etc. > > Best of Luck, > CM Lubinski > - --- > http://cmlubinski.info > > > matt donohue wrote: >> I'm missing the simple in your explanation or the reality of 3000 columns (Seriously???). >> Do you project the schema on the side of buildings? >> >> >> >> ________________________________ >> From: Anacreo <an...@gm...> >> To: Chicago PHP User Group <chi...@li...> >> Sent: Tue, April 20, 2010 8:40:23 PM >> Subject: Re: [chiPHPug-discuss] Data Models >> >> Yeah I don't have time for this... I've received similar poo poo >> responses from this group on similar type discussions. >> >> I'll tell you I'm a performance EXPERT and have been designing large >> scale systems for over 15 years. >> >> The general arguments have always been that the approach is too simple >> or too not what you guys are doing. Try working on a large scale and >> you'll realize ruby on rails and PHP with training wheels isn't quite >> the only answer. >> >> As for arbitrary numbers its not so arbitrary your code should meet >> your demands. When I signed up I didn't realize this group was PHP >> for pea-pod size projects with a pea-pod brained Arlo. >> >> Alec >> >> On 4/20/10, matt donohue <mm_...@ya...> wrote: >>> I'm resisting the urge to respond to this... or the 3000 columns. >>> >>> >>> >>> ________________________________ >>> From: Steve Gadlin <sg...@wc...> >>> To: Chicago PHP User Group <chi...@li...> >>> Sent: Tue, April 20, 2010 4:40:22 PM >>> Subject: Re: [chiPHPug-discuss] Data Models >>> >>> I'm a big fan of one table design. When I need a second table, I often >>> create an entirely separate database for it, just to keep things separate. >>> I don't like it when my tables "touch." Viruses spread easier when they do. >>> >>> Hi Arlo. >>> >>> Steve >>> >>> >>> On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: >>> >>>>> I wasn't being sarcastic at all Arlo I was giving you a design pattern >>>>> for >>>>> working with very large customizeable and extensible data set, sorry if >>>>> you >>>>> can't wrap your head around this design. >>>> Well ... sure, I can wrap my head around it, but all the superlatives like >>>> "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, >>>> large >>>> numbers and the generality of your "elements" example made it sound like a >>>> joke ... my mistake then. The Onion video is still funny, though. >>>> >>>> Cheers, >>>> -Arlo >>>> >>>> _______________________________ >>>> >>>> Arlo Leach >>>> 773.769.6106 >>>> http://arlomedia.com >>>> >>>> The killer app for the iPad >>>> http://washboardapp.com >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> _______________________________________________ >>>> chiPHPug-discuss mailing list >>>> chi...@li... >>>> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >>> -- >>> Steve Gadlin >>> Web Development Manager >>> Weigel Broadcasting >>> 312.660.8375 >>> sg...@wc... >>> >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> _______________________________________________ >>> chiPHPug-discuss mailing list >>> chi...@li... >>> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >>> ------------------------------------------------------------------------------ >>> _______________________________________________ >>> chiPHPug-discuss mailing list >>> chi...@li... >>> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >>> > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFLzn8Afzi1OiZiJLARAjiDAJ0Xxtg2xJOdN/sX7Nkp4wkRivRG3ACdE9H4 > XE+5BWXG/aCmRsRUSLQFgnE= > =WIIa > -----END PGP SIGNATURE----- > > ------------------------------------------------------------------------------ > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss |
|
From: junkmail <jun...@2z...> - 2010-04-21 16:04:23
|
Thanks for all the great thoughts! If anyone has ANYTHING else on this subject, I would greatly appreciate the info. Alec, that schema design is very interesting. I've seen similar write-only setups for extremely high performance systems. It sounds like the type and sub-type may be more specific to your application domain, but I see the utility of it. The main concept I see here is using a giant table with another table to describe the custom fields. You'll never have to modify the database schema directly, but you can change the meaning of the fields on the fly and there's very little overhead to have a bunch of extra columns, correct? The entire reason for using a data model is to answer the question "What if I want to use something else for my data storage". My hope is that one day to move towards a system similar to what Alec explained because it handles many problems fairly elegantly such as auditing, performance and a flexible schema. For now I am stuck with relational databases and an existing schema of a proprietary system. I am creating a web interface that extends the functionality. Lubinski, you're describing something extremely close to what I've currently put together. As for the second system, it sounds more like you're describing an EAV system - AKA tagging with arbitrary data types. That's a very effective approach, but I'm not sure how to handle any kind of fast searchability of a system like that. I've created programs that use that for just text field entries, but I haven't taken it any further because of the search issue. Thanks for the suggestions on the ORM's Jason, I'll definitely be digging into them to see what's good and bad. I modeled a lot of my current interfaces from CakePHP, but they don't seem to be very progressive in the database area. Torpor seems like it will be able to fill in those gaps. |
|
From: Jason R. <ja...@ho...> - 2010-04-21 04:51:16
|
Not sure about all of that. I know of a few people who mostly lurk on the list and respond to a few things that use PHP in large production environments. I must say a little mea culpa for seeing a worthwhile question and not taking the time to respond. That being said I dont think the list is about frameworks for intranet sites. Alec's system is pretty ugly in certain respects but I can see cases where it would work well. I dont think its a great general solution but I dont think he was pitching it that way. As to the original question I looked at the various ORM's out there (which is similar to the path you started down) and, yes, ended up writing my own because I wanted to abstract away data persistence not SQL statements, meaning that at my data persistence layer consisted of at the very least file, MySQL, Lucene and specialized mem-based dbs and I wanted a common interface. Maybe in the future it would also have CouchDB, Casandra Mongo or some other models in there as well. I did look at: Doctrine Propel CoughPHP and a few others. CoughPHP seemed the best for what I wanted but it was still tied to a SQL database. I will share a disagreement I have with my friend Harper and that is on the subject of JOINs.. He says no joins at all (and thats how Threadless runs) while I say joins are like memory allocation, there are times when a smart developer needs them and can make good use of them, but rarely and with caution. We both fall on the side of less JOINs the better. A model that makes it hard to do JOINs is not a bad thing. It will make your developers avoid them. So I wouldn't add complexity to your interface to enable arbitrary JOINs, I would instead add an escape hatch in your interface where you can override or extend specific instances easily for the _few_ cases where JOINs are truly good. I may be open-sourcing some of the components I've developed over the years and I'd be happy to give a talk on it at the next meeting.. whenever that is?.. Anacreo wrote: > Yeah I don't have time for this... I've received similar poo poo > responses from this group on similar type discussions. > > I'll tell you I'm a performance EXPERT and have been designing large > scale systems for over 15 years. > > The general arguments have always been that the approach is too simple > or too not what you guys are doing. Try working on a large scale and > you'll realize ruby on rails and PHP with training wheels isn't quite > the only answer. > > As for arbitrary numbers its not so arbitrary your code should meet > your demands. When I signed up I didn't realize this group was PHP > for pea-pod size projects with a pea-pod brained Arlo. > > Alec > > On 4/20/10, matt donohue <mm_...@ya...> wrote: >> I'm resisting the urge to respond to this... or the 3000 columns. >> >> >> >> ________________________________ >> From: Steve Gadlin <sg...@wc...> >> To: Chicago PHP User Group <chi...@li...> >> Sent: Tue, April 20, 2010 4:40:22 PM >> Subject: Re: [chiPHPug-discuss] Data Models >> >> I'm a big fan of one table design. When I need a second table, I often >> create an entirely separate database for it, just to keep things separate. >> I don't like it when my tables "touch." Viruses spread easier when they do. >> >> Hi Arlo. >> >> Steve >> >> >> On 4/20/10 4:37 PM, "Arlo Leach" <ar...@ar...> wrote: >> >>>> I wasn't being sarcastic at all Arlo I was giving you a design pattern >>>> for >>>> working with very large customizeable and extensible data set, sorry if >>>> you >>>> can't wrap your head around this design. >>> >>> Well ... sure, I can wrap my head around it, but all the superlatives like >>> "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, >>> large >>> numbers and the generality of your "elements" example made it sound like a >>> joke ... my mistake then. The Onion video is still funny, though. >>> >>> Cheers, >>> -Arlo >>> >>> _______________________________ >>> >>> Arlo Leach >>> 773.769.6106 >>> http://arlomedia.com >>> >>> The killer app for the iPad >>> http://washboardapp.com >>> >>> >>> ------------------------------------------------------------------------------ >>> _______________________________________________ >>> chiPHPug-discuss mailing list >>> chi...@li... >>> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> -- >> Steve Gadlin >> Web Development Manager >> Weigel Broadcasting >> 312.660.8375 >> sg...@wc... >> >> >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> ------------------------------------------------------------------------------ >> _______________________________________________ >> chiPHPug-discuss mailing list >> chi...@li... >> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss >> > |
|
From: matt d. <mm_...@ya...> - 2010-04-20 21:59:12
|
If you haven't used that column in a year it's time to throw it out. ________________________________ From: Arlo Leach <ar...@ar...> To: Chicago PHP User Group <chi...@li...> Sent: Tue, April 20, 2010 4:37:49 PM Subject: Re: [chiPHPug-discuss] Data Models > I wasn't being sarcastic at all Arlo I was giving you a design pattern for working with very large customizeable and extensible data set, sorry if you can't wrap your head around this design. Well ... sure, I can wrap my head around it, but all the superlatives like "wonderful," "fascinating," "brilliant" and "fun" plus the arbitrary, large numbers and the generality of your "elements" example made it sound like a joke ... my mistake then. The Onion video is still funny, though. Cheers, -Arlo _______________________________ Arlo Leach 773.769.6106 http://arlomedia.com The killer app for the iPad http://washboardapp.com ------------------------------------------------------------------------------ _______________________________________________ chiPHPug-discuss mailing list chi...@li... https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss |
|
From: Richard L. <ce...@l-...> - 2010-05-18 04:24:32
|
I tend to try to keep things as simple as I can, but if I expect a project to get medium-sized... Lately, I've been using a Query object, which serves two roles: 1. A singleton for the database connection. 2. All queries live here, as static methods, and return arrays of objects or whatever the calling routine most needs. Because all the queries are in one place, I don't have to troll through files to try to find that slow query. Since all the DB-specific code is in one file, I know I can more easily swap it out if I have to. If there are specific data I expect to put into memcache or other data store, I'll abstract that further and handle it specially. When I first saw this, I hated it, but after giving it a try, I'm starting to like it more. Not sure it's the *best* answer, and for a massive project with thousands of different queries, it would need to be broken up or at least organized in the codebase. I do that anyway, physically grouping the methods for any given class/table/module, even for a handful of methods per model/class. On Thu, April 15, 2010 5:19 pm, junkmail wrote: > I am curious what people use for fetching data in PHP. I've been > calling > this my model. > Over the past few years I've slowly built up a framework that > encompassed basic mysql_connect() and mysql_query() wrapping for error > handling and simplicity. > For the next step I made a basic query generator for 1-table CRUD > operations using simple table definitions in associative arrays. > The next step is for complex CRUD with table joins. This is the hard > part that I'm not 100% sure how I should tackle it. I'm thinking it > will > start small, but ultimately I'll move to full-blown relationship > modeling with the ability to perform CRUD operations across any > relationship. > My goal is to write little-to-NO hardcoded SQL into my PHP. > > I'm curious to hear about any strategies on this problem, or any > insights that may have been encountered in similar endeavors. > > Thanks! > > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > chiPHPug-discuss mailing list > chi...@li... > https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss > -- Some people ask for gifts here. I just want you to buy an Indie CD for yourself: http://cdbaby.com/search/from/lynch |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X