Here are my responses to your questions:
- Whenever you look at two tables and see a Many to Many relationship, you can solve the problem easily using a linker table. Also known as a junction table “is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, transition table, crosswalk, associative entity or association table.” Wikipedia example You saw me use these tables in your previous question. In this case you are stating that an actor can be managed my many Theater Companies and A Theater Company and also manage many Actors. This is a many to many so if you created a link table in between those tables for every relastionship between the two you’d add a new row in the link table that only contains a theater Company id and an actor id. If an actor was managed by many theater companies then you’d add several rows to the link table each holding the same actor id but each row having a different theater company’s id.
- Yes, you can have start_Location point directly to place. This means that that Start_Location attribute must be a Foreign Key (FK) pointing the theater company to the Primary Key (PK) of the related Place record.
- By all means an actor can be born in a place, but just like above, you need a column in Actor, that is a FK to the Place Table’s PK. You could call this column Birth_Place and all it’d hold is the PK of the record in Place that relates to the actor’s birth place. This column would also need to be NOT NULL because all actor’s need a Birth_Place.
- So far it seems like your diagram will work to solve this problem, yes. Just see question 1’s answer for that follow up addition.
-
You’re getting good at removing redundancies. Your diagram looks good. The only suggestion, I’d make is why do you have a play table and then 3 separate play type tables? Why not add them together in on Table called Play. It’d sit exactly where Play currently sits in your diagram and contain the same attributes it already does, but you also add the following:
a. Type – Would be a string that you could place “Drama”, “Comedy”, or “Tradegy” in so you’d know exactly what type of play it is. Also this would allow you to add future play types to the plays table and not have to add a whole new table to the DB.
b. Sub_Type – Would also be a string and hold the type that you currently have under the separate tables. They are all essentially the same attribute in each table and would just hold different type descriptors depending on the parent Type.
c. Main_Character – Would be a string that holds the main character, because in your three separate tables, you have main characters. You’re just calling them 3 separate things. (get the direction I’m going in here? )
d. Secondary_Character – Would be a string that holds the secondary character. You have a secondary character in your dramas and comedies, but non in your tradegies so in tradegy records this column would wind up being null. See what I did there? You now have one table where you used to have 4, and in that one table you can retrieve all the same information you had in those 4 separate tables. Hopefully that’ll make your life easier.
- You can do whatever you like, but I’m assuming you mean by best practices and it would be generally considered best practice to separate this single attribute into it’s Simple attribute sub parts. I.E. make it a composed attribute.