How to work with multiple tables and not get duplicate data? (MySQL / PDO)

Question

I am trying to create a firearms site that will host about 1,000 guns. These are not many records in the database, but I try to keep the database light as high as possible. I created five tables, while maintaining normalization, and I am having problems placing data in all five tables in one query. My database is built like this:

+-----------------+ +-----------------+ +-----------------+ +-----------------+ | make + | model | | image | | type | +-----------------+ +-----------------+ +-----------------+ +-----------------+ | PK | make_id | | PK | model_id | | PK | model_id | | PK | type_id | +-----------------+ +-----------------+ +-----------------+ +-----------------+ | | make_name | | | make_id | | | image_path | | | type_name | +-----------------+ +-----------------+ +-----------------+ +-----------------+ | | type_id | +-----------------+ +------------------+ | | caliber_id | | caliber | +-----------------+ +------------------+ | | model_name | | PK | caliber_id | +-----------------+ +------------------+ | | cost | | | caliber_name| +-----------------+ +------------------+ | | description| +-----------------+ 

This may be a normalized LLP, but this is what I work with;)

Let me show the code:

the form

 <form action="post" method="addProduct.php" enctype="multipart/form-data"> make: <input type="text" name="make" /> model: <input type="text" name="model" /> type: <input type="text" name="type" /> caliber: <input type="text" name="caliber" /> cost: <input type="text" name="cost" /> desc.: <input type="text" name="description" /> Image: <input type="file" name="image" id="image" /> <input type="submit" name="submit" value="Add Item" /> </form> 

addProduct.php

 $make = $_POST['make']; $model = $_POST['model']; $type = $_POST['type']; $caliber = $_POST['caliber']; $cost = $_POST['cost']; $description = $_POST['description']; $image = basename($_FILES['image']['name']); $uploadfile = 'pictures/temp/'.$image; if(move_uploaded_file($_FILES['image']['tmp_name'],$uploadfile)) { $makeSQL = "INSERT INTO make (make_id,make_name) VALUES ('',:make_name)"; $typeSQL = "INSERT INTO type (type_id,type_name) VALUES ('',:type_name)"; $modelSQL = "INSERT INTO model (model_id,make_id,type_id,caliber,model_name,cost,description,) VALUES ('',:make_id,:type_id,:caliber,:model_name,:cost,:description)"; $imageSQL = "INSERT INTO image (model_id,image_path) VALUES (:model_id,:image_path)"; try { /* db Connector */ $pdo = new PDO("mysql:host=localhost;dbname=gun",'root',''); /* insert make information */ $make = $pdo->prepare($makeSQL); $make->bindParam(':make_name',$make); $make->execute(); $make->closeCursor(); $makeLastId = $pdo->lastInsertId(); /* insert type information */ $type = $pdo->prepare($typeSQL); $type->bindParam(':type_name',$type); $type->execute(); $type->closeCursor(); $typeLastId = $pdo->lastInsertId(); /* insert model information */ $model = $pdo->prepare($modelSQL); $model->bindParam(':make_id',$makeLastId); $model->bindParam(':type_id',$typeLastId); $model->bindParam(':caliber',$caliber); $model->bindParam(':model_name',$model); $model->bindParam(':cost',$cost); $model->bindParam(':description',$description); $model->execute(); $model->closeCursor(); $modelLastId = $pdo->lastInsertId(); /* insert image information */ $image = $pdo->prepare($imageSQL); $image->bindParam(':model_id',$modelLastId); $image->bindParam(':image_path',$image); $image->execute(); $image->closeCursor(); print(ucwords($manu)); } catch(PDOexception $e) { $error_message = $e->getMessage(); print("<p>Database Error: $error_message</p>"); exit(); } } else { print('Error : could not add item to database'); } 

Therefore, when I add an item using the above code, everything works fine, but when I add another item using the same manufacturer name, it will duplicate it. I just want him to understand that he already exists, and not duplicate it.

I was thinking of putting some type of check to make sure that this data already exists, and if that happens, then do not enter the data, but get the identifier and enter it in other tables where necessary.

Another thing that I was thinking about is to create a drop-down list for the data that is likely to be duplicated and assign the value as an identifier. But my simple mind cannot find a better way to do this :( I hope all this makes sense, if not I try to clarify.

Answer 1

If you have fields where there will ever ever be a limited data set (the caliber is definitely one, and I suspect that manfacturer will also work), you can pre-populate the tables in the database and turn them into a search field.

In your HTML form, you can print a selection box instead of an input text field. The select value is the identifier in the search field - you don’t have to worry about adding anything to the search fields in the database.

When I had to do this in the past, I wrote a function to insert data; it checks if the value is in the table. If so, it returns the index of the field; otherwise, it adds it as a new record and returns the identifier for the new record. This is not the most elegant solution, but it works well.

Answer 2

You need to find out what constitutes a unique (non-duplicated) brand, model, type and caliber.

Then you need to create unique indexes for those tables that provide uniqueness. See http://dev.mysql.com/doc/refman/5.0/en/create-index.html

For example, you can use make_id, model_name and caliber_id to uniquely identify the model. You will need

 CREATE UNIQUE INDEX UNIQUEMODEL ON MODEL(make_id, caliber_id, model_name) 

to set up your unique index. Note that the primary key index may be a unique index, but you can also have other unique indexes.

Then you can use INSERT ON DUPLICATE KEY UPDATE to populate the tables. See here: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

In order for all this to work correctly, you need to make sure that before you try to fill each row of the model , you need the corresponding rows of type , caliber and make : you need the identifiers from the first three tables to fill the fourth.