Parse/regex one field after query using PHP or store segmented data in multiple fields?

views:

answers:

Parse/regex one field after query using PHP or store segmented data in multiple fields?

Should I plan to parse/regex, for example, an entire url string retrieved from one field of a database table during a while loop of query results using PHP?

Or should I store segments of the data (directory, filename, extension) in their own separate fields and concatenate the results during a while loop of query results?

Assuming worst-case scenarios, what would the most effective overall approach be in terms of both speed and efficiency?

MySQL & PHP are the suspects in question

+2 A:

My general philosophy is that you shouldn't have "compound" fields in your database. By this I mean where one columns contains two or more notional values.

So if you were only ever interested in "filename" then that's a reasonable single value column. If however you are often concerned with file extension, path and perhaps other data then you would store those in three (or more) columns.

Perhaps the worst example of this I see is where people implode an array with commas and then store that in a single column.

cletus 2010-02-26 02:16:41

i agree with you.. would you still hold to that answer considering the extra effort involved with insert queries rather than a select query? its the other side of the story, but equally influencing for the initial design...

CheeseConQueso 2010-02-26 02:49:35

@Cheese: what extra effort?

cletus 2010-02-26 03:40:25

either setting up the stored procedures or writing out the whole insert/update statements. and also the parsing on the way in before those insert/update statements are run

CheeseConQueso 2010-02-26 03:59:21

@Cheese Not sure I follow. If you need the file extension, etc then store them separately. Selecting is easy: just concatenate the pieces. Inserting is trivial as parsing the filename into its parts is also trivial. I wouldn't do either in the database (with stored procs, etc).

cletus 2010-02-26 04:12:47

lets say you grab a url via php and want to store it... you would either have to split it up before sending it to the table, or set up a stored proc that would do the same... or you could store it whole and then parse it into pieces where needed

CheeseConQueso 2010-02-26 04:43:46

+1, In my book, splits are more time consuming for the system, you have to parse the string, break it apart, etc. Concatenating the string together is almost nothing for the system. As a result, do you want to split the string one time before the insert or every time you load the data? no brainer for me. if you need to look at parts of the link, store them parsed out, this will help if you need to search for parts of the link.

KM 2010-02-26 13:37:39

good points... i was uncertain for a little and just wanted to make sure that storing them as segmented as possible was better for overall system performance

CheeseConQueso 2010-02-27 00:21:34

ansaurus

tags:

views:

answers:

Parse/regex one field after query using PHP or store segmented data in multiple fields?

related questions