SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot YAHA's Avatar
    Join Date
    Jan 2008
    Location
    MD, US
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Creating ID Columns

    Hello,

    I have a large table in MySQL that contains data like PartNumber, Price, Category, Subcategory, Manufacturer, etc, etc about various products. Unfortunately, this table doesnt contain any key or ID columns at all. This makes it very hard to create any kind of business logic for the website. For instance, I need to create a few ways to browse through products on the website. This could mean: by Manufacturer, by Category-Subcategory, and maybe the combination of the two. Since none of Categories, Subcategories, or Manufacturer columns have a corresponding ID column assigned, it is nearly impossible to have two bound <select> boxes for Category, Subcategory lookup. Manufacturer column offers some issues as well. So the question is, given the abovementioned setup, do you think I will have to create ID columns for Cat, Subcat, and Manufac? One thing I need to add is that I update this table on weekly basis from a CSV file (thus the initial absence of ID columns). Is there a way to assign ID's during the process of transferring data to MySQL from CSV or maybe after the data is already in the table?

    Any input is greatly appreciated !!!

    P.S. If you would like to see the code I use to transfer data from the CSV file to the table, just ask. Also, none of the abovementioned columns are unique. In other words, there are numerous products in each Category, Subcategory, and Manufacturer.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what do those columns use if they don't use id numbers? whatever it is, you can still generate <select> boxes using the values, but i'd have to know more about the range of values before recommending whether you should try to create separate tables for Cat, Subcat, Manufac, etc.

    id columns aren't necessary, just sometimes convenient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot YAHA's Avatar
    Join Date
    Jan 2008
    Location
    MD, US
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The columns dont use anything really. The table is just full of columns and is totally self-sufficient. There are just words in every column. As to the select boxes, I was trying to make them work in Coldfusion 8 using some AJAX methods and that doesnt seem to work without IDs (apparently, you cannot use the <select>'s display column to pass as an argument to the second <select> because it needs value).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    huh?? what's wrong with this --

    <SELECT name="bikeparts">
    <OPTION value="tires">tires</OPTION>
    <OPTION value="tubes">tubes</OPTION>
    <OPTION value="quick release headlamps">quick release headlamps</OPTION>
    </SELECT>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can write a script to import your information to denormalized tables on daily bases or weekly.

    You will see extremely performance boost ot the mysql server when make separate table for categories and manufacturers and just place a foreign key in items table. JOINS will work fast and you will browse your application with pleasure

  6. #6
    SitePoint Zealot YAHA's Avatar
    Join Date
    Jan 2008
    Location
    MD, US
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing at all at least, in theory . . . . I have tried that [passing the actual column value as a value of select] and somehow have problems getting the cfselect to populate, it just shows up empty. However, when I duplicate the example with a datasource that comes with coldfusion installation, it all works (the datasource has ID column as a bind reference). It is possible that the problem lies elsewhere . . . I just thought I would ask of common ways of working a table like this for a website

  7. #7
    SitePoint Zealot YAHA's Avatar
    Join Date
    Jan 2008
    Location
    MD, US
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kuzmanin View Post
    You can write a script to import your information to denormalized tables on daily bases or weekly.

    You will see extremely performance boost ot the mysql server when make separate table for categories and manufacturers and just place a foreign key in items table. JOINS will work fast and you will browse your application with pleasure
    Could you elaborate? What are denormalized tables? I get the separate table concept with foreign keys (actually, thats how I would do this myself if I had to design the structure from the beginning, but I am only given the excel file with bunch of columns). I am also familiar with JOINS.


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
  •