How to get rid of OTLT

hyperbole · #1 (**permalink**) 02-15-06, 11:02

I have a database with the following tables:

show_list - a list of the shows we've produced.
crew_members - an n:m table connecting shows and people
people - a list of people's names
job_title_list - a list of job_titles, Actor, Director, etc.
job_descriptions - This is the table that has the problem. It contains additional information about a person's job in a show.

Code:

   show_list
      show_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      title      VARCHAR(255)

   crew_members
      show_id    INT UNSIGNED NOT NULL DEFAULT 0,
      person_id  INT UNSIGNED NOT NULL DEFAULT 0,
      job_title_id INT UNSIGNED NOT NULL DEFAULT 0,
      job_description_id INT UNSIGNED NOT NULL DEFAULT 0

   job_descriptions
      job_description_id   INT UNSIGNED NOT NULL DEFAULT 0,
      description_type_id INT UNSIGNED NOT NULL DEFAULT 0,
      description            VARCHAR(255)

The job_description_id in the crew_members table is the ID of one or more records in the job_descriptions table. A record in the job_descritions table can be of type "character", "job", "comment", (these are, of course, the names of the description_type_ids). These are the only values allowed in this field and I have no plans to change that.

For example, when placing an Actor into the crew_members table, I would add two new records to the job_descriptions table. One with the name of the character they played and another with a comment about their performance. Both of these records will have the same job_description_id so that they can be referenced from the actors record in the crew_members table.

Last week I was reading some comments made by someone on a database forum and there was a reference to an article about the One True Logical Table. After reading the article, I realized that I had an instance of OTLT in my database and I have, since then, been trying to find information about whether I should try to remove it and if so how.

Is there a better way to implement this table? Should I try to remove the OTLT condition here?

Thanks,
Carl.

r937 · #2 (**permalink**) 02-15-06, 11:50

you could do it like this:

Code:

   crew_members
      show_id    INT UNSIGNED NOT NULL 
    , person_id  INT UNSIGNED NOT NULL 
    , character_id   INT UNSIGNED NULL
    , job_id         INT UNSIGNED NULL
    , comment        INT UNSIGNED NULL

   job_descriptions
      description_id INT UNSIGNED NOT NULL 
    , description     VARCHAR(255)

then each person would appear only once per show in the crew_members table, i.e. the PK is {show_id,person_id}

hyperbole · #3 (**permalink**) 02-15-06, 12:12

That's an interesting suggestion. I hadn't thought to do it that way.

I must not have been clear in my original explaination. At the present time, each person only appears once per show in the crew_members table. Their record may have a job_description_id which may point to more than one record in the job_descriptions table.

r937: What your suggesting is to create a new record ID for each of the record types and store that ID in the appropriate field in crew_members. Doesn't this leave job_descriptions as a OTLT since it's still storing multiple types of records?

Carl.

r937 · #4 (**permalink**) 02-15-06, 12:23

Quote:

Originally Posted by hyperbole

At the present time, each person only appears once per show in the crew_members table. Their record may have a job_description_id which may point to more than one record in the job_descriptions table.

i don't see how this is possible, unless their job_description_id is a comma-delimited list, in which case you really should change that design, it's not even first normal form

hyperbole · #5 (**permalink**) 02-15-06, 14:21

The job_description_id is not a unique key. So when I enter a row into the crew_members table, I can create several rows in the job_descriptions table that are all refered to by a single job_description_id.

For example, Christopher Reynolds played several roles in "Man of La Mancha". When I add a record for Christopher in the crew_members table, it will contain the show_id for "Man of La Mancha", Christopher's person_id, the job_title_id for "Actor", and a job_description_id, lets say 100. Now I can enter rows in the job_descriptions table with (100, "character", "Don Quixote de La Mancha"), (100, "character", "Miguel de Cervantes"), (100, "character", "Alonso Quijana"), (100, "comment", "as Chris Reynolds").

In this way the single row in the crew_members table points to four rows in the job_descriptions table.

Carl.

r937 · #6 (**permalink**) 02-15-06, 14:59

okay, i see (even though i think you have some copy/paste or typo errors in your examples)

well, good luck with it

hyperbole · #7 (**permalink**) 02-15-06, 16:09

Thanks for taking the time to look at it.

I'm still left with the questions: Is this an example of OTLT and should I try to remove the OTLT condition since this seems to be working?

Thanks again,
Carl.

r937 · #8 (**permalink**) 02-15-06, 16:18

is it an example? yes

should you get rid of it? that's up to you, and your estimation of (a) the benefit of doing so, and (b) the effort of doing so

andrewst · #9 (**permalink**) 02-18-06, 05:45

Quote:

Originally Posted by hyperbole

I'm still left with the questions: Is this an example of OTLT and should I try to remove the OTLT condition since this seems to be working?

I would say this is more like EAV than OTLT (follow the link in my sig. line for explanation and condemnation of both)

It seems that your job_description_id is actually a redundant synonym for (person_id,show_id), since (following your example) presumably only one person (Christopher) will ever have played those 3 characters with the comment "as Chris Reynolds", so what you really have is a table where you can enter supplementary information about a person in a show in an EAV form. It may as well have (person_id,show_id) as the key.

I can't say I like it, but if it serves your purposes so be it.

hyperbole · #10 (**permalink**) 02-18-06, 13:04

Tony,

Thanks for your reply. Actually your article on OTLT and EAV was one of the first I read on this subject. I have gone back and re-read it several times and I think I get a little more out of it each time as my understanding increases.

I see what you mean about job_description_id being a synonym for (person_id, show_id). I had not looked at it that way before. A very useful comment I shall keep in mind in future projects.

I agree with your statement "I can't say I like it, ...." That is the reason I started this thread in the first place. I don't like the way this table works. It seems to have the potential to grow into a monsterous table with lots of un-related data.

What is the best way to store the descriptions? Should each of the description types be given its own table? The nice thing about storing it all in the job_descriptions table is that it can be retrieved with a single query rather than needing to make a separate query for each type of description data.

Carl.

andrewst · #11 (**permalink**) 02-18-06, 13:44

I agree that having various types of description accessible from one table is appealing. However, some of these descriptions are actually more significant than that would suggest - e.g. character names. Also, querying won't be as easy as it could be with this design, if you imagine possible future queries. For example: "who has ever played the part of Don Quixote de La Mancha?" I think with your design the SQL would be something like this:

Code:

select p.name
from   people p
join   crew_members c
  on   c.person_id = p.person_id
join   job_descriptions j
  on   j.job_description_id = c.job_description_id
where  j.job_description_type_id = 'character'
and    g.description = 'Don Quixote de La Mancha';

But what if you had entered the character description as just 'Don Quixote' in one of the records? It wouldn't show up. I would prefer to see "characters" as a table in its own right so that my query would be:

Code:

select p.name
from   people p
join   crew_members c
  on   c.person_id = p.person_id
join   crew_member_characters cmc
  on   cmc.person_id = p.person_id
join   characters ch
  on   ch.character_id = cmc.character_id
where  ch.name = 'Don Quixote de La Mancha';

The character 'Don Quixote de La Mancha' only exists once, but may be linked to many crew_members via crew_member_characters.

Of course my design might be wrong, as I don't know your requirements. But I would use entity/relationship modelling to determine the data model and then build tables based on that.

hyperbole · #12 (**permalink**) 02-19-06, 14:18

Tony,

Thanks a lot for your explaination. That helps me to understand some of the problems I've been having and how to solve them.

I was not familiar with the term "entity/relationship modelling" so I did some searching and came up with a number of sites that discuss it. For anyone interested, these are the two I found most useful:
Database eLearning
University of Texas.

Tony and r937, thanks again for your help. I have a much better understanding of this problem.

Carl.