SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Store array or delimited string in DB?

    Hello,

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

    My predicament:

    (page1.css)

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

    (page2.css)

    Code 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)

    Code 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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937! Many thanks for the help, I totally appreciate your pro help.

    Quote Originally Posted by r937 View Post
    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:

    Code:
    ----------------
       | 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:

    Code MySQL:
    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

  4. #4
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

  6. #6
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi oddz! Thanks for the reply and for sharing your pro advice!

    I did find some interesting info on stack overflow:

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

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

    Code:
    ------------------------------------------
       | 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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

    Quote Originally Posted by r937 View Post
    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

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

  10. #10
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):

    Code MySQL:

    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

  11. #11
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  12. #12
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi SpacePhoenix! Thanks for the reply, I really appreciate your help.

    Quote Originally Posted by SpacePhoenix View Post
    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:

    Code:
    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

  13. #13
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  14. #14
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •