Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Database design

  1. #1
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Database design

    Here is an exmaple of data that could be put in a database table / tables.

    http://www.etown.co.nz/nzPlist.htm

    'all digital cameras', but assuming there were other categories of equipment for sale as well perhaps 'cd players','monitors' and 'light bulbs' or something.

    How would you design, set out the tables? how many, for which and what data types?


    I would think:

    ProductCategory table
    catergoryID
    catergoryName


    Products table
    ProductID
    Brand
    Model
    Price
    EffectivePixel
    Sensor
    Zoom
    ..whatever is appropriate for products in other catergories i.e:
    monitor size
    wattage etc


    BrandName table ??
    BrandNameID
    BrandNameTitle


    I'm not sure if the brandname table should be there? in this case alot of the cameras are from the same brand although you could just have this as a field in the products table, you would be repeating the brandName quite a bit,

    Also for things like 'BrandName', 'Model' and 'categoryName' etc, I would specify 'TINYTEXT' this is something like 1-255 in length i think, would it be a performance hit to have TEXT(100) instead if you knew the data would be under 100 Character which in the before mentioned is probably true?

    I just wanted to get some suggestions and info, insight on how others would do this, I've never really spent time on the actual structure of my tables and have always just gone for same old datatypes whether they are truely appropriate or not. Since I've only mostly been playing around thats o.k but when creating a commercial site this could of course make a huge difference to performance. There is quite a bit of mySQL/SQL documentation around but not much 'real-life' type info or examples to learn from. it's more like "this datatype supports numbers only and.."

    Perhaps tips from anyone who has done this for an e-commerce type website. How you would go about it in the case of this example?

    mat,
    Last edited by mat; 11-29-2002 at 07:33 AM.

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Do a search on google for "database normalization" that should help you out a little.

  • #3
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the tables worked out, I have a few questions one which datatypes to use

    for these columns and similar:


    catergoryName
    Model
    Price
    EffectivePixel
    otherFeature
    Sensor
    BrandName

    which contain a bit of text/numbers but not too much should I use
    something like:

    VARCHAR(100)

    or

    TINYTEXT

    or

    TEXT(100)


    ?


    and for the column 'overview' which will contain an overview of that product probably 4 or more paragraphs of text should i use
    blob?


  •  

    Posting Permissions

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