![]() ![]() So here's a model of what your database should look like. It seems like what you need is more of a conceptual start here. Once this is set up, it is easy to query and find everything a given person has done, or everything a person has done as a director, or everyone who has ever directed a movie, or all the people involved with one specific movie. If you didn't want this, then instead of the film_genre table, films would just contain a genreid. I'm also showing genre as a manymany relationship, because possible a film is in multiple genres. You might also have a role_details field in the film_people table, which could contain extra information depending on the role (eg, the name of the part the actor is playing). ![]() and it would look more like: films => **filmid**, title, otherstuff.įilm_people => **filmid, personid, roleid** The role table would hold various positions - eg, director, producer, star, extra, grip, editor. So rather than even have director and actor tables, you could have a single person table, and join that table in using a role table. Taking it a step further, sometimes directors are also actors, and vice-versa. so there would be a many-to-many relationship between films and directors, so you'd need a table with eg: films_directors => **filmid, directorid** You can really take this design quite far, and it's all a matter of figuring out what you want to be able to store in it.įor example, rather than have a single director per film, some films have multiple directors. It also allows you to freely change the name, without worrying about the foreign keys stored in other tables (the ID stays the same, so you don't have to do anything). ![]() Matching an integer is much faster than matching a string. Using a surrogate key (primary key) for tables is generally a good idea. You obviously don't want to enter the director's birth date every time you enter a film that this person directs, so it makes sense to have a separate entity for a director.Įven if you didn't want to store all this information about the director (you just want their name), having a separate table for it (and using a surrogate key - I'll get to that in a second) is useful because it prevents typographic errors and duplicates - if you have someone's name spelled wrong or entered differently (first,last vs last,first), then if you try to find other movies they've directed, you'll fail. Perhaps you'll want to store the director's first name, last name, date of birth, date of death (if applicable), etc. It is better to denormalize this and store the year in the film table itself.ĭirector, on the other hand, is different. In this case, having a year table is unnecessary, since there are no other attributes about a year, besides the year itself, that you would store. While it can be good in some circumstances, it can be unnecessary in others (as generally it makes queries more complicated - you have to join everything - and it is slower). Having a separate table for certain things, let's use director, as an example, is called normalizing. In database jargon, entity = table, attribute = field/column. An attribute is more like a piece of describing information. You have to make a distinction between attributes and entities. Here is what I'm thinking for the database design:įilms Table => filmid, filmtitle, runningtime, descriptionīut, how would I go about creating relationships between these tables?Īlso, I have created a unique ID for the Films Table with a primary key that automatically increments, do I need to create a unique ID for each table?Īnd finally if I were to update a new film into the database through a PHP form, how would I insert all of this data in (with the relationships and all?) I would like to create relationships between these to make it more efficient but don't know how. I have the following information that I want to include: Say I want to create a database to hold my DVD collection. I am using MySQL and here is my question: I'm trying to get my head round this mind boggling stuff they call Database Design without much success, so I'll try to illustrate my problem with an example. ![]()
0 Comments
Leave a Reply. |