Not the best with MySQL, but I'm scraping data from websites with python, and storing the results in a mysql database. All the data I've collected have been placed into custom objects that I created (i.e each object is a car with a respective make, model, year, etc.) Now where I'm having an issue is that each car has a number of images associated with it, and it can range from zero to ten images. The question I have is how can I store these images in a MySQL table and connect it to its respective car.
This is the table I've created for each car so far (python code):
command = ("""CREATE TABLE `%s`(
`auctionHouse` varchar(255) NOT NULL,
`auction` varchar(255) NOT NULL,
`lot` varchar(255) NOT NULL,
`year` int,
`make` varchar(255) NOT NULL,
`model` varchar(255),
`description` text,
`fullName` varchar(255) NOT NULL,
`salePrice` varchar(255),
`highPrice` int NOT NULL,
PRIMARY KEY (`auction`,`lot`));
"""
)%(auction)
Do I have to create another table for holding images and then when I query the car, use a join?
1 Answer 1
You do not have to create another table, but I would STRONGLY suggest you do. You specified that you have a finite number of images per car, so you could have 10 columns in your car table. But what happens when you decide to add more?
If you plan to store your images as Blobs, then most definitely don't create 10 columns; go ahead and create another table.
If you choose to create another table, I would suggest using Innodb and specify the foreign key constraint.
I would also suggest that you use a transaction when when you create the car entry, and associate the images to that car.
You need the id of the car you created to use it as the value in your image table to relate the image to the corresponding car.
http://www.w3schools.com/php/php_mysql_insert_lastid.asp
Then you can use a Join to get all the images related to the car, or you can query the images table by itself if you only need the images and you have the car Id.