MySQL Performance and Best Practises - Many Tables vs 1 Large Table?

[posidyn]

Hello Everyone.

A database with one table with all data in it or a database with many on-the-fly generated tables with less data in each table.
Which is better for performance and which is better practise?

Eg.
I want a database that stores people's names and what cars they have owned and duration of ownership.

Option 1.
Have 1 large table that stores all the information and as people add themselves or more cars, a new entry is added to the 1 large table.

Option 2.
Each person that registers creates a new table (dynamically created) and stores one person's cars in that one table.
There will be a separate table that stores which person uses which table in the database.


Which option above is best for performance and is best practise?

Or is there something else that is even better than the above?

Thank you.

 

[edkolis]

Neither of these options is optimal.

You should have two tables: one for people, and one for cars. The car table should have a foreign key column referencing the person table which specifies who any particular car belongs to.

Read up on "database normalization" to learn how to decide these sorts of things for yourself!

 

[posidyn]

Neither of these options is optimal.

You should have two tables: one for people, and one for cars. The car table should have a foreign key column referencing the person table which specifies who any particular car belongs to.

Read up on "database normalization" to learn how to decide these sorts of things for yourself!

In my current example, if names of cars are in one table and names of people are in another table, where does duration of ownership go? One person can own many cars over different durations and one car type can be owned by many people over different durations. Does this mean there needs to be a third table that links the person table to the car table? If this third table becomes becomes very large, is it OK?

I will now go read up on "database normalization". Will probably take me a while.

 

[edkolis]

So the same car can be owned by many people, then? Yes, then you would need a third table, since this is a many to many relationship, and the duration would indeed go in that third table.

The third table becoming large is not a huge issue, unless you are already dealing with huge numbers of cars or owners to begin with. You might want to consider what happens when person A owns a car, then sells it to person B, and then person B sells it back to person A - your database design should allow this to happen, and not allow only one ownership record for person A with that car.

 

[Skizzerz]

The goal of this is to represent the data you want appropriately, not for "optimization" -- there are ways to optimize MySQL that do not require changing the best way to represent your data. A good rule of thumb should always be to have one table per "noun" in your application (in your case, one table for people and one table for cars). Then, if you have any relations between the nouns, there should be tables between that as well (e.g. a table for "which people own which cars"). Optimization should be done using keys and indices on what you will be looking up the most (to speed lookup times) as well as removing duplicate information (which is achieved via the above setup I mentioned where you aren't saying the person's name, email, address for each car they own). Furthermore, you can implement some form of caching to further speed things up, although this is difficult to do effectively on Free Hosting.

Example schema (assumes we want to store the name, email, and address of people, and the year, make, model, average price, and color of cars. Note that the color is put into the relation of person_owns_car since that way we do not need to replicate year/make/model information for each color. Furthermore, we assume that (name, email) results in a unique person and (year, make, model) results in a unique car. For the sake of person_owns_car, we also assign each person and car a numeric id as a unique identifier as well. We cannot assume that (person, car) or even (person, car, color) is unique in person_owns_car because of the many to many relationship present (a person can own multiple cars, and a car can be owned by multiple people, so we make this an index instead of a unique key). Please note that I have not tested these so the syntax may be off, but the general idea is still there. InnoDB is important because MyISAM (the default) does not support FOREIGN KEY constraints.

Obviously your described schema is different than this example. This is merely to give you an idea of how to construct your own schema, not to do the work for you.

CREATE TABLE people (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(256) NOT NULL,
    email VARCHAR(256) NOT NULL,
    address VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY person (name, email)
) ENGINE=InnoDB;

CREATE TABLE cars (
    id INTEGER NOT NULL AUTO_INCREMENT,
    year INTEGER NOT NULL,
    make VARCHAR(256) NOT NULL,
    model VARCHAR(256) NOT NULL,
    avg_price FLOAT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY car (year, make, model)
) ENGINE=InnoDB;

CREATE TABLE person_owns_car (
    person INTEGER NOT NULL,
    car INTEGER NOT NULL,
    color VARCHAR(256) NOT NULL,
    INDEX person_car (person, car),
    FOREIGN KEY (person) REFERENCES people(id),
    FOREIGN KEY (car) REFERENCES cars(id)
) ENGINE=InnoDB;

 

[posidyn]

Thank you all for your help.
This information is very useful to me.