Help with relational database for a TV station

[J. Hai]

I work at a college television station, and am trying to build a database to keep track of the following:
- Student members and staff
- Workshops and who's taken which ones
- Video library
- Link between library table and members table, showing members involved with many programs and programs crediting many members (think a mini-IMDb.com)

This is my first time using MS Access, or building any relational database for that matter. After reading up on normalization, one-to-many, many-to-many, junction tables and so on, I jumped in and created a rough draft of the database. The resulting relationships model is attached below.

Can you tell me what I'm missing? I also have two specific questions:

1) For Categories and Sub-Categories of videos, what I really want is to draw from the same list (e.g. out of Art, Sports, Documentary, Multicultural, etc. the Category might be Art, and Sub-Category Multicultural). What's the simplest way to achieve this?

2) I'm not sure if I got the junction tables between the Members and Library tables right. How do I take into the account the possibility that there might be more than one camera operator shooting for the same program, for example?

Thanks for your help!

 

[Call_Me_Sam]

Hi, Pat..with regards to the cat - subcat tables, when you say m-m do you mean for the design to be changed so there are simply two tables Category and SubCategory and no link between them (i note that subcat does not have any defined name so seemed meaningless) and the resulting two tables would have ID and Name?

 

[Rabbie]

Hi, Pat..with regards to the cat - subcat tables, when you say m-m do you mean for the design to be changed so there are simply two tables Category and SubCategory and no link between them (i note that subcat does not have any defined name so seemed meaningless) and the resulting two tables would have ID and Name?

Sam, take a proper look at what Pat posted

You need only two tables. Role which defines Director, Producer, Audio, etc. Whatever job can be done and a second table which is used as the junction table. It contains just the three key fields -StudentID, RoleID, and CatalogID.

You will obviously need a student table as well to hold their details. By using a junction table you will find it easy to have co-directors or multi-tasking students. Please post if you need more clarification

 

[Call_Me_Sam]

thanks Rabbi, but i feel i did, though as J. points out they are new to Access and i felt that maybe putting it in simpler terms would have been the order of the day. (though not to the point of insulting).. i was only asking Pat for clarity myself.

personally, i don't use the term Junction Box, so this was alien to me...

My Bad...for the purpose of being PC for Rabbi... Junction Table...and for the record i wasn't being sarcastic, just typing in the midst of a complete office move 500m down the road. Apologises to anyone who thinks i was..

 

[Rabbie]

thanks Rabbi, but i feel i did, though as J. points out they are new to Access and i felt that maybe putting it in simpler terms would have been the order of the day. (though not to the point of insulting).. i was only asking Pat for clarity myself.

personally, i don't use the term Junction Box, so this was alien to me...

The expression is Junction Table and is the standard way of modelling a many to many relationship in a relational database.

It is always hard to get the correct balance in a reply between being condescending and assuming too much knowledge on a newbies' behalf. Pat always seems to get it right and I thought her explanation was very clear.

 

[Call_Me_Sam]

Rabbi, please note i have edited my last with an explaination. Again i apologise if my seeking clarity for my own knowledge was too much for this thread.

 

[Rabbie]

Rabbi, please note i have edited my last with an explaination. Again i apologise if my seeking clarity for my own knowledge was too much for this thread.

Sam please accept my apologies if you think I was being critical. That was not my intention. I always feel it is a good thing if people query things they are not sure about rather than making incorrect assumptions so always feel free to ask. It also helps to make sure we express ourselves better.

 

[Call_Me_Sam]

Sam please accept my apologies if you think I was being critical. That was not my intention. I always feel it is a good thing if people query things they are not sure about rather than making incorrect assumptions so always feel free to ask. It also helps to make sure we express ourselves better.

No worries Rabbi, i'm knee deep in network issues from the office move right now so prob did sound off. I admit i am now googling Junction Tables as like say not sure about their uses. I remember being scarred by my lecturer stating that m-m relationships have no place in a normalised database, and have simply stuck to 1-m and 1-1. But i will honestly make it my business to expand my knowledge.

thanks

edit: NO i get it now...see i am looking..it is what i thought it was, but by including the junction you in effect eliminate the m-m relationship, making a 1-m on both sides...i just never knew it as a Junction Table..might have been called a Link Table..

 

[neileg]

Acess doesn't support m-m relationships directly, so you have no choice but to use a junction table. There's some good stuff on many to many and junction tables in these forums, too.

 

[J. Hai]

1. rather than having categories and subcategories, use a m-m relationship which will allow you to place something in as many categories as is appropriate. This will make searches simplier.
2. The junction you have is complex and not flexible enough. You need only two tables. Role which defines Director, Producer, Audio, etc. Whatever job can be done and a second table which is used as the junction table. It contains just the three key fields -StudentID, RoleID, and CatalogID. This allows you to support co-producers and co-directors, etc. It also allows one person to play multiple roles. It also means that you don't need to define a new table if you add a new role. Just add the new role to the role table and you look brilliant

That worked out beautifully - thank you! I think I've gotten the hang of simulating m-m relationships in Access through 1-m relationships and junction tables.

Attached is a new model with your suggestions implemented. I also expanded the database to include information about staff and officer positions members can hold at the station. How does it look now?

Something I've noticed in other people's models that's missing from mine are those little 1 and infinity icons, which I presume are meant to signify "one" and "many". Are those important, and if so, how do I make them appear?

Thanks again for your help!

 

[Call_Me_Sam]

Morning all,

just for my own clarity this time..but would J. really need a LibraryCategoryID in the Junction Table, when you could set the primary keys to be the CatalogID and the CategoryID? I am only asking from the perspective of design and performance. My thinking is that it would ensure that a Library record did not have a duplicate category set against it.

Please i am open to correctness about this one.

 

[neileg]

just for my own clarity this time..but would J. really need a LibraryCategoryID in the Junction Table, when you could set the primary keys to be the CatalogID and the CategoryID?

Not essential. I would use one, but I can't give you a strong argument why!

I am only asking from the perspective of design and performance. My thinking is that it would ensure that a Library record did not have a duplicate category set against it.

You can achieve the same effect by applying a two field index set to no duplicates. It doesn't have to be a PK.

 

[Call_Me_Sam]

Not essential. I would use one, but I can't give you a strong argument why!
You can achieve the same effect by applying a two field index set to no duplicates. It doesn't have to be a PK.

Would you er away from using PKs as this table should really have the FKs in thus use the field index instead? I guess what i'm asking is that under the skin we're trying to design relational database system, and using Access as the tool..but from what i'm reading lately is that the tool doesn't necessarily implement the relational model that well..So i'm trying to get under the skin of Access to see if it effects performance etc by doing things that might have the same effect but in different ways..am i making sense..

 

[neileg]

Not quite sure what you're asking.

Access supports indexes on its tables. These can be single field indexes or multi field indexes. They can allow duplicates or prevent them. A primary key field is always indexed and always set to no duplicates. An index properly applied will improve performance but if you index everything, the overhead of maintaining the indexes is greater than the performance improvement.

Some developers look for a natural key in the data. This allows an existing field or combination of fields to act as the primary key. Some developers prefer to use a meaningless primary key (i.e. an autonumber). I'm in the latter camp because too often a seemingly unique natural key ends up with duplicates somewhere down the line.

Access suports a relationship between tables by holding the PK from one of the tables as a FK in the other. This directly allows one to one (though these are rare) or one to many relationships. By using a juction table, you can model a many to many relationship. There's no issue about this and no compromise. What Access can't model well is fuzzy relationships.

 

[Call_Me_Sam]

Not quite sure what you're asking.

Access supports indexes on its tables. These can be single field indexes or multi field indexes. They can allow duplicates or prevent them. A primary key field is always indexed and always set to no duplicates. An index properly applied will improve performance but if you index everything, the overhead of maintaining the indexes is greater than the performance improvement.

Some developers look for a natural key in the data. This allows an existing field or combination of fields to act as the primary key. Some developers prefer to use a meaningless primary key (i.e. an autonumber). I'm in the latter camp because too often a seemingly unique natural key ends up with duplicates somewhere down the line.

Neil, this is the sort of answer i was looking for you hit that nail on the proverbial. Sorry if my question seemed strange or even dumb, but if i don't ask i don't learn. Neither do others who read these posts. (guess this is why i get myself in trouble sometimes) I guess i have tended to look for natural keys and not made productive use of the index(es). many thanks

 

[neileg]

Happy to have helped.

 

[Rabbie]

Neil, this is the sort of answer i was looking for you hit that nail on the proverbial. Sorry if my question seemed strange or even dumb, but if i don't ask i don't learn. Neither do others who read these posts. (guess this is why i get myself in trouble sometimes) I guess i have tended to look for natural keys and not made productive use of the index(es). many thanks

Also worth remembering that indexes on numeric fields are usually more compact than indexes on text fields. This often makes them quicker.

 

[Call_Me_Sam]

Also worth remembering that indexes on numeric fields are usually more compact than indexes on text fields. This often makes them quicker.

thanks Rabbi, sorry we got off on the wrong footing the other day. You peps are w wealth of knowledge.

but let me ask, i should really set indexes instead of creating a PK of a number of fields that would in effect prevent duplicate records, this would have a improvement on performance. I ask as i have a table with at least 4 PKs in order to prevent duplication of records, but not all of them link off to other tables therefore no real FKs. If i'm along the right lines, i think i may invest a little time in altering a redesign i'm currently planning. thanks again

 

[Rabbie]

thanks Rabbi, sorry we got off on the wrong footing the other day. You peps are w wealth of knowledge.

but let me ask, i should really set indexes instead of creating a PK of a number of fields that would in effect prevent duplicate records, this would have a improvement on performance. I ask as i have a table with at least 4 PKs in order to prevent duplication of records, but not all of them link off to other tables therefore no real FKs. If i'm along the right lines, i think i may invest a little time in altering a redesign i'm currently planning. thanks again

You're welcome. Good luck with our redesign. Remember the best way to learn is by making mistakes and then getting it right.