Store array or delimited string in DB?

[mhulse]

Hello,

I am writing this simple web app that produces CSS pages (views) based on user input.

My predicament:

(page1.css)

.foo { height: 50px; }
.baz { height: 22px; }
.bing { height: 13px; }

(page2.css)

.foo { height: 50px; }
.bing { height: 13px; }
.baz { height: 22px; }
.bar { height: 48px; }

A third page might be a combination of the above two css pages:

(page3.css)

.foo { height: 50px; }
.baz { height: 22px; }
.bing { height: 13px; }
.bar { height: 48px; }

Note: Duplicate css has been merged.

In terms of data, page1.css and page2.css are both generated from multi-dimensional arrays. With that said, page3.css will be a combination of page1.css and page2.css array data.

With me so far?

My question:

In terms of database storage, should I store the 1) original array data, 2) raw CSS, or 3) a delimited string?

In other words, when my script creates the third page, it has to combine pages one and two... How should I store the data from the first and second pages in the database in order to generate a third, combined, css page?

I hope that makes sense...

I am not looking for code, I am just curious 'bout best practices. Seems like maybe a delimited string would be a good option... Or would storing the full CSS page and applying string replace functions be just as fast? Or, would storing the original arrays be the best option? It seems like storing the original arrays would be the option with the least amount of fuss (I already have the PHP to parse the arrays and generate a CSS page), but is storing array data in the DB not good practice?

Any tips ya'll could send my way would be great!

Many TIA!

Cheers,
Micky

[r937]

store the array, but don't store it ~as~ an array (databases don't directly support arrays anyway), just as rows in a normal table

for page 1, store 3 rows, for page 2 store 4 rows, and then when you go to do page 3 you will retrieve rows WHERE page = 1 OR page = 2 and use DISTINCT to get 4 rows

[mhulse]

Hi r937! Many thanks for the help, I totally appreciate your pro help.

store the array, but don't store it ~as~ an array (databases don't directly support arrays anyway), just as rows in a normal table
for page 1, store 3 rows, for page 2 store 4 rows, and then when you go to do page 3 you will retrieve rows WHERE page = 1 OR page = 2 and use DISTINCT to get 4 rows

Ah, interesting! I did not even think about doing that! Thanks for kicking me in the right direction.

So, in terms of what the CSS table might look like:

----------------
   | page | val  |
------------------
1  |  01  |  50  |
2  |  01  |  22  |
3  |  01  |  13  |
4  |  02  |  50  |
5  |  02  |  13  |
6  |  02  |  22  |
7  |  02  |  48  |
8  |  03  |  50  |
9  |  03  |  22  |
10 |  03  |  13  |
11 |  03  |  48  |

And the query might be something like:

SELECT DISTINCT page, css 
FROM css  
WHERE (
	page = 01
	OR page  = 02
)

Please keep in mind, I am just learning this stuff... Please do not laugh if I did anything wrong in the above pseudo logic/code.

Thanks again r937!!! I really appreciate your help and expert advice!

Cheers,
Micky

[mhulse]

I should clarify:

• There could potentially be hundreds of different css pages.
• The average rows per css page would be 75.

Does this info change the approach you suggested?

Thanks again for the help!

Micky

[oddz]

I think that is a bit of an overkill. I don't see any problem with storing a serialized array for each page of CSS. So given 3 pages you would have three rows. Each row would contain a serialized array of the CSS data.

[mhulse]

Hi oddz! Thanks for the reply and for sharing your pro advice!

I did find some interesting info on stack overflow:

$title = base64_encode(serialize($array) );
$title = unserialize(base64_decode($mysql_data) );

So, in that case, the data might look like:

------------------------------------------
   | page | val                        |
------------------------------------------
1  |  01  |  serialized encoded array  |
2  |  02  |  serialized encoded array  |
3  |  03  |  serialized encoded array  |
------------------------------------------

I plan on caching the css pages (using CodeIgniter framework) so maybe the overhead of parsing serialized/encoded array would not be that bad?

One concern I have is the comment about database normalization on the Stack Overflow site:

Arrays do violate normalization; ...

Thanks to both of you for the expert feedback.

Cheers,
Micky

[r937]

storing a serialized array has the disadvantage that it makes searching for a particular value cumbersome and slow -- you basically have to retrieve every row and inspect it to see if it contains the value you're looking for

in database terms this is painful, although i daresay that for only a few hundred rows, you won't really notice the difference

but who here among us has not asked at some point "gee i wish i knew which page uses this particular css class" and then had to look at each one -- tedious, innit

[mhulse]

Hi!

storing a serialized array has the disadvantage that it makes searching for a particular value cumbersome and slow -- you basically have to retrieve every row and inspect it to see if it contains the value you're looking for

in database terms this is painful, although i daresay that for only a few hundred rows, you won't really notice the difference

but who here among us has not asked at some point "gee i wish i knew which page uses this particular css class" and then had to look at each one -- tedious, innit

Hmm, those are some good points.

I am just learning mysql, so I can use all the feedback and pro advice that I can get... I think I will experiment with both approaches for the sake of learning new things.

Either way, I will post a link to my app if/when I get it to a usable point.

Thanks again!

Cheers,
Micky

[oddz]

Normalizing CSS is going to become a disaster. If there is no need to ever access declaration or rule data at the database level then separating everything out into separate tables is a huge overkill and will be management nightmare. If all you need to do is store the data to rebuild the page then just store the array in a serialized format. You'll thank yourself later.

[mhulse]

Hi oddz! Thanks for the reply.

I have tried the serialized approach to start out with... So far the table is looking well organized:



Both 'container' and 'margin' are primary keys. I opted to not include an primary key "id" column (not sure if this is good practice or not):

CREATE TABLE my_table(
	container INT NOT NULL,
	margin INT NOT NULL,
	data TEXT NOT NULL,
	tm timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY(container, margin)
)

Essentially, the user will input a container/margin and the css page is based on those values. When the next user requests a page, if it (container/margin) is not already in the table, then a new entry gets added.

I am still experimenting with both your guys ideas... Thanks so much to both of you for the feedback.

Micky

[SpacePhoenix]

How many sets of css are there? If each css set stays the same, perhaps one possibility would be to store the css in separate files in a BLOB field in the database.

[mhulse]

Hi SpacePhoenix! Thanks for the reply, I really appreciate your help.

How many sets of css are there? If each css set stays the same, perhaps one possibility would be to store the css in separate files in a BLOB field in the database.

Here is an example of what the encoded "data" array looks like:

Array
(
    [container] => 990
    [margin] => 10
    [foundation] => Array
        (
            [0] => 990
            [1] => 970
            ...<snip>...
            [78] => 15
            [79] => 10
        )
)

So, for each container/margin, the "foundation" array is different.

Because I am storing the original array, I can generate (and cache) the third page (i.e. combine the css, like I wanted to do in my original post) via the controller and/or the view (I am using the CodeIgniter php framework).

With that said, do you think I should use a blob? Or, do you use a blob to store static stuff (like CSS that does not change, but is needed on every page)?

Thanks for the help!
Cheers,
Micky

[SpacePhoenix]

How much of the css is common to all the pages? Keep the common css stuff in a normal css file on the server and for each pages css, keep the CSS stored in a BLOB field. echo the css specific for the page to the style section of the HTML page.

[mhulse]

How much of the css is common to all the pages? Keep the common css stuff in a normal css file on the server and for each pages css, keep the CSS stored in a BLOB field. echo the css specific for the page to the style section of the HTML page.

Ah, I see! Thanks for the clarification.

I am currently storing the common css in an external file (like you suggest), but I have yet to look at using blobs. Hmmm, maybe I could store both the blob'ed css and the orignal css array -- The best of both worlds!

Thanks for the tips! I may be back with more questions.

Cheers,
Micky