SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    i need ur opinion with mysql structure building!

    hi ..

    i got new site which have more than 20 main category and each main category have about 5 subcategory, and ech subcat. have a form, and each form have 10-30 input (some inputs are frequent).

    now what is the best way to do the mysql structure ?

    should i store all this data in 1 table coz they are frequent inputs ?

    or
    should i store the each main categories in table ?

    or

    should i store each form in table ?


    which way is faster with query and search ?


    i hope some1 can give me usefull way !


    thx ...

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sean, which one?

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Files, Databases and XML

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't quite decipher the whole text, but to me a parent/child relationship is what you are describing.

    Depending on how you wish to normalize you can have two tables or one.

    One table method:
    category( category_id, parent_id, .. )

    Two table method:
    category( category_id )
    category_parent( parent_id, child_id )

  6. #6
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it`s not category problem !

    it`s about the db structure, what is the best way to do it ?


    just counted how many inputs have all the forms, it`s about 80, this mean i need about 82 column , so how should i buide the db tables for this inputs ?

    e.i ..
    main cat. :
    Lands
    ---> sub cat : lands for buiding (30 input 'option')


    i have 20 main cat. and each one have 2-5 subcat !


    any1 can understand me plz !

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    richi, you say it's not about the categories, and then you say you have 20 categories, each with 2 to 5 subcategories...

    so tell us, what is it about? i mean, disregard the categories for a minute, what else is in this database? so far all you've said is "land" and "lands for building"

    that's not a lot to work with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry .. but really am so confused about this !

    the db is mysql and the lang. php , ok i`ll disregard the categories , but i must talk about the categories , coz the records depend of the categories !

    the problem that i have more than 80 record to store and organize in the tb .

    i was thinking to do it with this way :

    Table #1 advert :
    id
    cat --> category.id
    date

    Table #2 advert_des
    id
    aid ---> advert.id
    .
    .
    .
    . etc


    i`ll store all the records in 1 table, and i don`t know is the the best way ?

    or should i store each advert_des alone in table (by main category) ?

    which way faster to query and seach ?

    i hope it`s clear now ...

    thx

  9. #9
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Commercial lands :
    Space land (10 Rec)
    For Buildings (10 Rec)
    For Commercial complexes (17 Rec)


    Accommodation :
    Space land (15 Rec)
    Villa (34 Rec)
    Flats (20)
    ...
    ..
    ..
    ..

    and more ...

    this 2 main category and some sub cat. of it !

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i think i see the problem

    please read through data modeling and normalization

    this will help you identify your entities, and that in turn will help you in deciding which foreign keys are needed to relate your tables

    don't worry about categories and subcategories for the initial design, just think about categories as one entity type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx .. but i had read it befor .

    do u know with which structure is faster to query and search :

    1. 1 table include about 85 columns .

    2. many tables and each table include 10-35 columns .


    ?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one table is almost always faster to query than several, assuming eveything is indexed properly

    also one table is an easier structure to modify -- less "rework" on existing queries than if you had several tables

    good luck

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx alot !


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
  •