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 08:42PM

> "get away from the external data (author name) to the the internal id".
SELECT id FROM foo WHERE name = '...';

> Note: theme_page_template_name is a unique name for the page template but only within a particular theme_template.
That implies
UNIQUE(theme_template_id, theme_page_template_name)
which might be a useful index.

> Folders
You keep mentioning "folders". In a database, a "database" is a collection of "tables", but that is as far as the "folder" analogy goes. A "table" contains lots of similar "rows" and dissimilar "columns", much like a spreadsheet.

Would you list what you have in the way of "mappings".
I mentioned Books, Authors, Persons, Directors, Actors, Movies as "Entities". Then I listed the relationships between them, and whether they were "one-to-many" or "many-to-many". Then I built "relationships" between the entities by either of two mechanisms:
1-many: The id of the 'one' table is a field in the 'many' table. It is definitely indexed (probably the PRIMARY KEY) in the 'one' table. It is probably indexed (not-UNIQUE) in the other.
many-to-many: This requires an extra table, which contains pairs of ids.

See if you can lay all that out.

With those relationships, you can wander from one table to another.

Then, to "get into" the maze of tables, you probably need some extra INDEXes (ISBN, name, module_url, etc).

For the UI, you may need to display the name of a template (page_template, whatever), but hide in the selection mechanism the id of the entity. The user sees one thing, the app acts on the other. You can dynamically create the choice list by doing something like
SELECT movie_id, movie_title FROM Movies WHERE genre = 'drama';

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.