Database help

[sorin21us]

I have to create a database for cars. I have to have in database the car, car type, description, drivers. I was thinking to put each one in a diff table. I am not sure about this so please, someone, tell me if is good or not:

table car_name:
car_id
car_name

table car_type:
type_id
car_id
car_type

table drivers:
driver_id
driver_name

table about_car:
id
car_id
type_id
driver_id
description

[megazoid]

I would change it a bit

table cars:
car_id
type_id << car has a type, not vice versa, so this field should be here
car_name
description << car has a description

table car_types: << it’s a collection of types, so only id and title
type_id
car_type

table drivers: << same as yours
driver_id
driver_name

car_drivers << not “about_car” because there is “cars” table for that
car_id
driver_id

[StarLion]

I agree with megazoid’s reorganization; let me give you the three steps to designing a database i use (and have used for… god how old is this post anyway? 5 years now?)

1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
2: Identify Properties of the Entities (User’s Name, Email, etc) [These will be fields in your tables]
3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]

In your case, you’re storing information about a Car, and a Driver. (Type is currently a one-field table, and could be reduced into the Car table)

Well, what information do you know about a car. It has a name, it has a type, it has a description. These are things intrinsic to each individual car. Those become the car table’s fields.

Driver? Well, the driver has a name. That’s all we’re storing about the driver at the moment. (Note that this constitutes a one-field table and might be reduced, but ‘name’ is not unique enough to qualify for reduction)

How are they related? A driver may drive multiple cars; a car may have multiple drivers. As this is a many-to-many matchup, we require a join table (car_driver) to link the two entities.

[sorin21us]

Thank you StarLion and megazoid. I forgot to mentioned that I want to learn the mysql joins, so I will not use Navicat anymore. But to do that I have to design the databases correct . Taking another example, and comparing with yours megazoid, I arrived to this database, lets say for jokes, with categories, subcategories, pages and users.
Please tell me if I designed in the right why to perform correct the mysql joins.

CREATE TABLE categories (
category_id SMALLINT NOT NULL AUTO_INCREMENT,
subcategory_id SMALLINT NOT NULL,
category VARCHAR(30) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE KEY category (category)
) ;

CREATE TABLE subcategories (
subcategory_id SMALLINT NOT NULL AUTO_INCREMENT,
subcategory VARCHAR(30) NOT NULL,
PRIMARY KEY (subcategory_id),
UNIQUE KEY subcategory (subcategory)
) ;

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
type ENUM(‘member’,‘admin’, ‘superadmin’) NOT NULL,
username VARCHAR(30) NOT NULL,
email VARCHAR(80) NOT NULL,
pass VARBINARY(32) NOT NULL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
date_expires DATE NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_modified TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
UNIQUE KEY email (email)
) ;

CREATE TABLE pages (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id SMALLINT UNSIGNED NOT NULL,
user_id SMALLINT UNSIGNED NOT NULL,
title VARCHAR(100) NOT NULL,
content_page LONGTEXT NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY category_id (category_id),
KEY creation_date (date_created)
);

[StarLion]

So I see a table for subcategory, but then nothing using it.

Also, you’ve gotten the relationship between category and subcategory backwards - a category can have multiple subcategories, but a subcategory can only have one category.

Alternatively, scrap the subcategories table entirely and recognize that subcategories are just categories that have a parent.

[sorin21us]

StarLion you mean to put the category_id in the subcategory table? Like this?

CREATE TABLE categories (
category_id SMALLINT NOT NULL AUTO_INCREMENT,
category VARCHAR(30) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE KEY category (category)
) ;

CREATE TABLE subcategories (
subcategory_id SMALLINT NOT NULL AUTO_INCREMENT,
category_id SMALLINT NOT NULL,
subcategory VARCHAR(30) NOT NULL,
PRIMARY KEY (subcategory_id),
UNIQUE KEY subcategory (subcategory)
) ;

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
type ENUM('member','admin', 'superadmin') NOT NULL,
username VARCHAR(30) NOT NULL,
email VARCHAR(80) NOT NULL,
pass VARBINARY(32) NOT NULL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
date_expires DATE NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
UNIQUE KEY email (email)
) ;

CREATE TABLE pages (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id SMALLINT UNSIGNED NOT NULL,
user_id SMALLINT UNSIGNED NOT NULL,
title VARCHAR(100) NOT NULL,
content_page LONGTEXT NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY category_id (category_id),
KEY creation_date (date_created)
);

[StarLion]

Correct - A one-to-many relationship in a database table is represented as a field in the ‘many’ table - in this case, subcategories. Because otherwise you couldnt store the data.

[sorin21us]

StarLion I am not so good with databases, that is why I have to ask you this: someone told me that each category should have his own table, for ex jokes, funny films, funny stories, each one with a diff table. The same with subcategories.
Will be that a better way to design the database?

[StarLion]

UNLESS each thing you’ve just described has unique properties that no other thing in the list has, that person would be lying to you. The question is #2 on the list: What do you want to store about each of these things? How do you want to display them?

[sorin21us]

The categories, jokes, funny films, funny stories, will be let’s say in the left menu. When I click on jokes, will display subcategories: sailors, blondes, etc. If I click on funny films I will see the subcategories: from usa, from canada, etc. So each category has diff subcategories.

[StarLion]

but thats not intrinsic to the item.

“My categories are X Y Z; They will each have subcategories”. The subcategories are not different from each other except that the value for their parent is different. They dont require different fields, and therefore can be lumped together in a single table.

[sorin21us]

Please one of you help me to understand how to do the second Select tag from the form.

This is the page: http://localhost/aproapetot-nou/add_page.php

The idea is when I click on Select category and select one(Bancuri is the only one fill in), on the second one Select tag I want to see the subcategories only from Bancuri. Each category, first Select tag should show diff subcategories, from the second Select tag. Like on the cars sites: you select a car, then from the second select, you select the model.
So this is the form:

Completeaza toate campurile:

    <p><label for="category"><strong>Categorie</strong></label><br />
    <select name="category"<?php if (array_key_exists('category',
    $add_page_errors)) echo ' class="error"'; ?>>
    <option>Select One</option>
    <?php // Retrieve all the categories and add to the pull-down menu:
    $q = "SELECT category_id, category FROM categories ORDER BY category ASC";
    $r = mysqli_query ($dbc, $q);
    while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
        echo "<option value=\"$row[0]\"";
        // Check for stickyness:
        if (isset($_POST['category']) && ($_POST['category'] == $row[0]) )
        echo ' selected="selected"';
        echo ">$row[1]</option>\n";
    }
    ?>
    </select><?php if (array_key_exists('category', $add_page_errors))
    echo ' <span class="error">' . $add_page_errors['category'] .
    '</span>'; ?></p>
    
    
    
    <p><label for="subcategory"><strong>Subcategorie</strong></label><br />
    <select name="subcategory"<?php if (array_key_exists('subcategory',
    $add_page_errors)) echo ' class="error"'; ?>>
    <option>Select One</option>
    <?php // Retrieve all the subcategories and add to the pull-down menu:
    $q = "SELECT
    categories.category_id,
    categories.category,
    subcategories.subcategory_id,
    subcategories.category_id,
    subcategories.subcategory
    FROM
    categories
    INNER JOIN subcategories ON categories.category_id = subcategories.category_id 
    WHERE subcategories.subcategory_id=". $_GET['id'] ." ORDER BY subcategories.subcategory ASC";
    $r = mysqli_query ($dbc, $q);
    while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
        echo "<option value=\"$row[0]\"";
        // Check for stickyness:
        if (isset($_POST['subcategory']) && ($_POST['subcategory'] == $row[0]) )
        echo ' selected="selected"';
        echo ">$row[1]</option>\n";
    }
    ?>
    </select><?php if (array_key_exists('subcategory', $add_page_errors))
    echo ' <span class="error">' . $add_page_errors['subcategory'] .
    '</span>'; ?></p>
    
    
    <p><label for="title"><strong>Titlu</strong>
    </label><br /><?php create_form_input('title', 'text',
    $add_page_errors); ?></p>
    
     
    <p><label for="content_page"><strong>Continut</strong></label>
    <br /><?php create_form_input('content_page', 'textarea',
    $add_page_errors); ?></p>
       <script type="text/javascript">
CKEDITOR.replace( 'content_page' );

    <p><input type="submit" name="submit_button" value="Adauga" 
     id="submit_button" class="formbutton" /></p>
</fieldset>