SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    PA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    New to MySQL, need help with Table Layout

    Ok, I have decided to tackle a database for my web redesign. After CSS and PHP I think I can handle it, but I have a simple question for starters.

    I plan on creating a database with a table for each section of the website. One table for products, one for "About Us", "Links"...etc...

    I plan on using php to query the DB and build my page so I am planning to go with the following fields in my tables (with data types and sizes):

    Index - Char(5)
    Title - Char(90)
    Description - Char (200)
    Keywords - Char (200)
    URL - Char (200)
    html - mediumblob

    Now for the question. Should I use the varchar data type for everything, since each field could vary in length except for index. Also is there a difference between mediumblob and medium text. I was going to go with blob, but one page is 65K characters (terms and conditions page).

    I was reading varchar can slow down things and if i go with mediumblob to accomodate one page will that slow things down? All in all there will be about 5 tables that will be used to generate 50 or so pages.

    Will this work ok, or am i missing something?

    Thanks...Mike

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, you're on the right track

    don't use reserved words (like INDEX) as column names

    what sort of values were you thinking of putting into the "index" field?

    make the datatypes VARCHAR(255), except for the one which will hold your content

    according to the mysql manual,
    The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.
    use as few BLOB/TEXT columns as you can get away with

    the keywords is potentially another problem -- if you were thinking of storing comma-delimited words in there, don't, set up a separate 1-to-many table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    PA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    no, you're on the right track

    don't use reserved words (like INDEX) as column names
    Good idea. Basically this will be a three digit value separated by dashes (i.e. 1-0-1, 2-1-0) which will be used to define the page I want to call.

    use as few BLOB/TEXT columns as you can get away with
    Yeah, I just need one. I will use it for the content for the given page. Initially I was going to keep it out of the DB, but figured i would keep it in.

    the keywords is potentially another problem -- if you were thinking of storing comma-delimited words in there, don't, set up a separate 1-to-many table
    Not sure I understand the one to many table. I was just going to put the META keywords to the page in there, without commas.

    Thanks...Mike

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh, okay, the meta tag, i get it

    (i was thinking of something else)

    yeah, varchar(255) will do for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    PA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    oh, okay, the meta tag, i get it

    (i was thinking of something else)

    yeah, varchar(255) will do for that

    Thanks....Now I can get started.....

    Mike

  6. #6
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    PA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more thing. I think I may change the way I lay out my tables. Initially I was going to have one table for each section of my website for a total of about 5 tables. Each table would have had the same values, but now i am thinking I should just use one or two.

    If i used one the fields would be:

    pageID - Unsigned Autonumber INT - Used as a page identifier
    parentID - Unsigned INT - Used to build a menu in conjunction with pageID
    Title - varchar(255) - Web Page Title
    Description - varchar (255) - META Description
    Keywords - varchar (255) - META Keywords
    URL - varchar (255) - Webpage URL
    html - mediumtext - Actual HTML content for the page

    With this in mind the way my pages will work is as follows:

    I will call a page with the PageID variable, the page that matches will use all the data in the given row. Then a menu will be built, which may use parentID, Title, PageID and URL. All the other fields will be unused.

    So, should I remain with my one table model, or have Two tables:

    i.e:

    Table A: PageID, ParentID, Title, Description, Keywords, URL
    Table B: HTML

    or

    Table A: PageID, ParentID, Title, URL
    Table B: Description, Keywords, HTML

    Or Am I totally overthinking this?

    Thanks...Mike

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, just one table will do the job
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    PA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    actually, just one table will do the job
    Thanks...Thats what I was thinking. Tops I will have about a 100 pages, so I don't think I will be hitting the wall anytime soon.

    Thanks....Mike


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
  •