I am building a simple CMS based on the Kevin Yank Database Driven Website books (I have 3rd and 4th edition). I really wish there was more discussion about lookup tables than the brief one page overview with one simple example.
Anyway, what I want to do is very close to his jokes db - I want to input notes on different creative projects - but I have one extra table to lookup. What do I do with my CHARACTER table as it is the same as the TYPE table - a many-to-many releationship with TEXT. Do I make a third column of IDs in PROJTYPE or a second lookup table that is PROJCHAR? If that’s the case how does that link to TEXT?
your diagram has a line from projtype to text, and i believe it should be from projtype to project
use the projtype table as a many-to-many relationship table ~only~ if you are sure that a project can be more than one type
if a project can be only one type, then typeid column belongs in the project table, assuming, of course, that the type table is intended to classify projects and not texts (as the line on the diagram would seem to indicate)
as for relating the character table, that all depends on whether you want to relate characters to projects or to texts
by the way, you shouldn’t really use reserved words like TEXT for table or column names
The connecting lines and arrows are out of the book - I scanned the tables layout page and changed the names in the tables for my purposes.
A Project is the only thing that there can only be one of - which is why it is connected to the “text” table - because each entry is a text entry.
I figured that a Project can have each of the types in the list so say I have a Project called My First Book and I have one note that says “scenario idea…chicken crosses the road” and then I have also under My First Book “quote” - “I want Mr. Smith to say… Why did he do it?” So I’m thinking that these are two distinct types of Project - I can sort by quotes or scenarios related to that book later. The quotes must involve a character, but the scenarios may involve several characters. How should I design this?
No, sorry I’ve confused you. When I say, “My Book” I’m just using that as an example -I want to consolidate my notes which are currently all text in a word.doc or hand written ideas on scraps of paper into a db. I have no images to input anywhere - these are writing ideas. Think of a series of books - each has ideas - characters - maybe the idea of a piece of text for a sign post somewhere.
The book that I got the chart from is Kevin Yank’s 3rd Edition Building Your Own Database Driven Website - I have the 4th edition as well which shows how to use controller files but since this is such a simple setup I’d prefer just to use the 6 linked tables without building a big CMS. I scanned his setup of lookup tables and overwrote it with my titles.
what you call a lookup table is usually called a relationship or many-to-many or association or linking or junction table (i prefer relationship table)
a lookup table is typically only use to translate a code into a full name
e.g. provinces table is a lookup table, has province code and province name, is referred to by various tables’ foreign keys, and is used in queries to look up the province code and display the province name
