Database design questions

[UncleRobin]

Over the last several years I’ve kludged together a database for my Asian Drama and movie watching history. It mostly works, and that’s mostly thanks to MUCH help from many contributors here. THANK YOU to all the helpful experts here.

I’m currently planning to build a new database from scratch, using Base as a GUI manager for an external Firebird, that I will create via Base. I have a few questions about this:

FIRST
If I create an external Firebird fdb in Base, can I import Calc data for the tables? I exported all the tables from my current odb as calc files some time ago , as a data backup measure and because originally I was planning to build a new internal odb. I created my current core tables from Calc imports, are there any caveats of issues to look out for if I want to do the same with an fdb created via Base?

SECOND
One of the main reasons I decided to create a new db from scratch was to add elements missing from my current one, especially actors, screenwriters and directors, and to separate trope tags from genres. I know this means a lot of many-t-many relationships, hence a lot of join tables.

At the moment, I have three separate lists: Actors, directors, and screenwriters. On one database forum, a user with a similar project received some responses encouraging them to add those roles simply as fields in a “persons” table. I can see that would cut down on data duplication, and give me more complete information on ach person, but I’m not clear on how to select from that field.

My current rough plan is to have a form with “director(s)” "screenwriter(s) and “actors” subforms. I could have a dropdown list and select the appropriate person in each category. If those 3 categories were fields in a “persons” table, how would I select them to populate the relevant fields in the form?

Rather than asking anyone to “do my homework for me”, I’m happy to be pointed to resources that could teach me how to solve this. I have several ‘dummies’ style books on SQL and couple of more challenging ones on database design, and would lvoe recommendations for other material that could help me get the hang of this. Which leads me to

THIRD
Can anyone recommend free (or VERY cheap) ERD software? As the above shows, I need to clarify my own thinking, and having offline ERD could be a big help, I think. Thanks for any and all suggestions, recommendationas or advice.

[CRDF]

@UncleRobin

On one database forum, a user with a similar project received some responses encouraging them to add those roles simply as fields in a “persons” table. I can see that would cut down on data duplication, and give me more complete information on ach person, but I’m not clear on how to select from that field.

A priori I think this is the best option.
“Persons”
Person_ID | type code (actor, …) | name
You would select as
SELECT […] FROM “Persons” WHERE “type” = ‘:insert_type_as_parameter’;

[UncleRobin]

THANK YOU! THat was so clear and simple even I could follow, much appreciated.

[CRDF]

EDIT
As in this Industry same Person can exerce multiple roles (actor, productor, director…) you should have table “Persons” WITHOUT this field!
Table “Roles”
| R_ID (the “type code”) | Role (actor, director, …) |
AND an intermediate (join) table for the multiple roles x persons
= many-to-many relationship.

[UncleRobin]

Thank you for this. So, one table with just Person info, and one with a list of roles, and join them. That’s straightforward, thanks.

It’s the number of joins I need to get clear in my head. With that “Roles” table, could I just add Drama-ID as an FK to link the person to their specific role(s) in a given production?

Related to that is the design of a data entry form. When I add a new Drama or movie, how I ensure that a new person is added to relevant tables?

This is why I said I need to do more reading to understand the process better. I have a subform on my current data entry form that I thought I’d setup to allow me to add new genres (see screenshot). but when I try, it doesn’t work - I can select genres already in the table from a dropdown, can’t add new ones. Since I’ll need to add new persons and roles often in my new database, clearly I need to do more reading and study to understand how to get this right

[CRDF]

@UncleRobin

one table with just Person info, and one with a list of roles, and join them.

No. By “roles” I meant the functions: actor; director; producer…
And an intermediate table for joinning these 2, as one person may be actor sometimes; a director or a producer sometimes…

@UncleRobin

When I add a new Drama or movie, how I ensure that a new person is added to relevant tables?
This is why I said I need to do more reading to understand the process better

Yes! You need to design the DB very carefully.
I think the first step should be these 3 tables.
Tables for Movies or Dramas, and role proper in each production follows.
OR you could link each person’s role to the specific Drama/Movie, as you said above. This way the “role” (fk) will go in this table (an not in that table “Roles” I mentioned).
You need to think carefully about the design. Mirroring the infos you want in the DB, of course. But you could want to be able to view select all “roles” any person had in his/her career, so…

[UncleRobin]

Thanks. Your comment highlights why I’m looking for easy to use ERD software. I have poor motor skills so pen and paper not really an option. Here’s a screenshot of my current DB relationship table. It works quite well, but I want to move away from embedded and add the personnel information.

[CRDF]

So, for that table “Dramas” you could link a table like, say
“Drama_Persons” to relate

1. the persons you want to associate to it (the Drama) – (fk) from the table “Persons”;
2. the roles each one had in it – (fk) from table “Roles” (best) or from a ComboBox list.

[Villeroy]

1. Transferring data from one db to another db with Base is a matter of drag&drop of table icons. A spreadsheet does not help here, mainly because spreadsheet supports text and number as the only data types.
2. Your table design is wrong as already outlined by others.
3. What is ERD? ERD - Wikipedia
   Entitiy relations model? Well, you have a relational database at hand. Every conservative SQL database engine supports one-to-one, one-to-many and many-to-many relations. Base forms provide a most simplistic (contrary to sophisticated) tool set to edit related records.

[UncleRobin]

Thanks Villeroy.

I don’t actually HAVE a table design for my new/next database yet, hence my questions here. I’m trying to get clear what kind of errors to avoid before starting, and that’s why the responses are so valuable. The basic purpose of ERD software is to be a sort of sketching pad, mapping out relations between the entities in a database. The English expression “measure twice, cut once” is a good analogy here. AN ERD can help measure the parameters of a database before ‘cutting’ to the chase and actually building it

[UncleRobin]

Thanmk you so much for your patient help. I think I get it, and will practice by building a tiny sample DB with just a handful of enmtries to see if I really do