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

Question

I am trying to create a firearms site that will house about 1000 guns. This is not a lot of database entries, but I am trying to keep the database light as possible. I have created five tables, keeping normalization in mind, and I'm having problems putting data into all five tables in one query. My database is structured like so:

+-----------------+ +-----------------+ +-----------------+ +-----------------+ 
|       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 might be TOO normalized, but this is what I am working with ;)

Let me show the code:

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');
}

So 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 it to realize it already exists and not duplicate it.

I was thinking of putting some type of check to see if that data already exists and if it does then don't enter the data, but get the id and enter that in the other tables where required.

Another thing I thought of was to create a dropdown for the data that will most likely be duplicated and assign the value as the id. But, my simple mind can't figure out the best way to do it :( Hopefully all that makes sense, if not I will try to elaborate.

Answer 1

You need to work out what constitutes a unique (unduplicated) make, model, type, and caliber.

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

For example you might use make_id, model_name, and caliber_id to uniquely identify a model. You'd need

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

to set up your unique index. Notice that a primary key index can be a unique index, but you can have other unique indexes as well.

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

For all this to work correctly, you'll have to make sure appropriate type, caliber, and make rows exist before you try to populate each model row: you need the ids from those first three tables to populate the fourth.