Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: Simple Database Design Problem


Advanced Search

Re: Simple Database Design Problem
Posted by: Rick James ()
Date: June 13, 2011 10:33AM

In the book-author example...
CREATE TABLE Books (
    book_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ISBN ...,
    title ...,
    etc
    PRIMARY KEY(book_id),
    UNIQUE(ISBN),
    INDEX(title)
);
CREATE TABLE Authors (
    author_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(...) NOT NULL,
    etc
    PRIMARY KEY(author_id),
    UNIQUE(name)
);
CREATE TABLE BooksAuthorMapping (
    book_id INT UNSIGNED NOT NULL,
    author_id INT UNSIGNED NOT NULL,
    PRIMARY KEY(book_id, author_id),
);
That is, you need some extra way to find a record -- ISBN is an obvious case for books; author `name` is perhaps correct for Authors, but it poses several problems -- spelling differences, two authors with same name, etc. Those problems are application problems; the database can help, but cannot solve it, except by leading you to a author_id, which is the database's unique identifier for the author.

I added the index on `title` to Books in case you are looking for a book and don't have the ISBN handy.

The sooner you can get away from the external data (author name) to the the internal id, the sooner there is smooth sailing.

If you search the Web for 'Rick James' you are likely to find a singer. I am a not he.

Sorry, I can't relate this to your situation -- I don't understand the differences among
Templates
Page Templates
Theme Page Templates
The names sound like a "is a" relationship -- A "page template" IS A "template". If this is the case, the Books-Author example is not a good model.

IS A -- This is better handled in the Movies model: An Actor ISA Person and/or a Director ISA Person. So, consider tables:
* Persons: person_id, name, birthdate -- PRIMARY KEY(person_id), INDEX(name, birthdate)
* Actors: actor_id, person_id, other info about the person as an actor
* Directors: dir_id, person_id, other info about the person as an director

Or, since there really isn't much to put into Actors and Directors, consider
* Persons: person_id, ...
* Movies: movie_id, ...
* Contributors (many-to-many): movie_id, person_id, role ("actor", "director", etc) -- PRIMARY KEY (movie_id, person_id, role), INDEX(person_id, role)
Note that a person can both direct and act in a movie, hence the PRIMARY KEY includes `role`.

Options: ReplyQuote


Subject Written By Posted
Simple Database Design Problem Filip Nedyalkov 06/09/2011 08:49AM
Re: Simple Database Design Problem Rick James 06/10/2011 11:48PM
Re: Simple Database Design Problem Filip Nedyalkov 06/11/2011 02:04AM
Re: Simple Database Design Problem Filip Nedyalkov 06/11/2011 02:07AM
Re: Simple Database Design Problem Rick James 06/11/2011 09:18AM
Re: Simple Database Design Problem Filip Nedyalkov 06/11/2011 04:56PM
Re: Simple Database Design Problem Rick James 06/11/2011 09:46PM
Re: Simple Database Design Problem Filip Nedyalkov 06/12/2011 03:23AM
Re: Simple Database Design Problem Rick James 06/12/2011 09:01PM
Re: Simple Database Design Problem Filip Nedyalkov 06/13/2011 03:04AM
Re: Simple Database Design Problem Rick James 06/13/2011 10:33AM
Re: Simple Database Design Problem Filip Nedyalkov 06/13/2011 04:53PM
Re: Simple Database Design Problem Rick James 06/13/2011 08:42PM
Re: Simple Database Design Problem Filip Nedyalkov 06/14/2011 03:28AM
Re: Simple Database Design Problem Rick James 06/14/2011 08:09PM
Re: Simple Database Design Problem Filip Nedyalkov 06/15/2011 02:22AM
Re: Simple Database Design Problem Filip Nedyalkov 06/15/2011 06:15AM
Re: Simple Database Design Problem Rick James 06/15/2011 07:31PM
Re: Simple Database Design Problem Sachin Gupta 09/27/2011 11:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.