Data Base “design” advanced help needed

Databases
1

I’ve already created a data base as I want to today, but it was 20 years ago, and I totally forgot how and found nothing in an internet search.

To explain what I want to do, I take StarGate (the TV serial) as an example, but it is valid for any other (say Friends, for example).

So, there are seasons, and I will create a TABLE I will call “Seasons”,
there are actors, and I will create a TABLE I will call “Actors”,
there are scripters, operators, director…, and I will create a TABLE I will call “Technicians” (a better word is welcome, eventually, “Crew,” maybe ?)…
etc. (if applicable !)

The idea is to create ona TABLE for all seasons, but also creating another TABLE (or two ?) for the actors (technicians ?).

Then, place a reference for each actor in the episode records instead of the actors names replicated in all Records (for the main actors).

This is what I forgot: how to set a reference to the Actors TABLE in the Episode TABLE.

In the UI design, I suppose that I can add a PopupMenu, populated from the Actors TABLE, but that is all I can think.

I think advices will unlock my brain for the other project I have in mind. I am collecting the data, so no waste of time. Sometimes, a single word is enough, sometimes not.

Advice(s) is/are needed. TIA

2

What you’re looking for is a many-many table to join the actors to the seasons.

3

I would do like this:

“Seasons” table (Id, SeasonName)
“Episodes” table (EpisodeId, SeasonId)
“Actors” table (Id, Name, Surname, etc …)
“ActorsInEpisodes” table (ActorId, EpisodeId)

playing with the JOINs, within the queries, you can also find which surroundings in the season, etc.

for technicians and more, the same principle applies.

4

Capture d’écran 2022-05-05 à 12.35.32
Capture d’écran 2022-05-05 à 12.35.32894×398 24.6 KB

1 Like
5

i would use a table participants(people) and this entry have flags for the activities.
because actors and technicans are both human with a skill.

season<-episode<-participants
person (actor,technic,…)

6

either I didn’t understand the situation well, or something is wrong with the diagram. If I insert in the same table the id of the actor and the id of the technician, will I have as a record all the combinations? or do you intend to leave fields to null? I would create two distinct tables: one to link actors to episodes, another for technical legaries to episodes. And as a key I would use the couple “actor id - episode id” on one side and the couple “technical id - episode id” on the other.

7

There are many ways to skin a cat and each has their pros and cons… Good database design recommends normalising data to avoid redundancy and inconsistency.

My preferred way would be to have a Persons table and a Roles table (1 to many) rather than seperate tables for Actors, Scriptures, Directors etc.

Very often the same person can be an actor, director, producer etc. This will allow you to have relationships between persons eg Father<>Daughter, Spouse with a Connections/Relationships table.

It will also allow simple creation of reports with a variety of criteria.

However, there will be many to many relationships between tables.

8

Yes i agree. Given the tenor of the conversation I had kept to “simple”, often more than enough to handle situations

9

Yes, a persons table and a roles table, with pointer back from the latter to the former. In a one-man show all roles will point back to the same person.

10

yes you fill either the tech or actor id and leave the two others id to null

11

I tried to stick to the OP askings. but yes this is another approach.

12

In the data diagram, there is a link between appears_in_episode and actors
It’s likely, looking at the diagram, that you might be tempted to repeat episode 1 and 2 in many different seasons.

So the appears_in table needs either to reference season AND episode IDs, or the Episodes table needs a unique ID for each records , and hold episode number as well as a title.

13

each yellow field is a primary unique key autoincrement.
each episode (unique) belongs to one season only.
so one actor “appears_in_episode” and by the episode-season link belongs only to one season.

14

i have a thought.
people can one day being a actor and tomorrow a technican and in next episode a cameraman.
somehow a frozen status at one day.

15

I have seen actors that became directors for some episodes and then even producers or executive producers.

16

Alec Guiness played 8 roles in “Arsenic and Old Lace”, IIRC.

17

Hello Emile,

I read really a lot of book about this topic and did a lot with databases.
It also depends about the kind of databases and the features it supports.
And maybe the tool / way you want to use to deal with the data.

And there is also the topic about security ( eg, in DB2 u can mask fields and more … )

So don’t forget not only to model your data also to keep all use cases in focus.

Sometimes its better to have one big fat database for reporting or screen output, sometimes a design as described in many books. I always use a ERD tool, which has also ( sometimes ) some build in assistants and checkers…

BR Rainer