Simple help needed

[canfish]

Hi all,



I have been working on a database for quite a while now, and I have a simple-ish question.

Say I have a table of facilities. I want to associate radio stations, newspapers, and television channels with those facilities. Some of these stations/newspapers/channels apply to more than one facility. Do I have to make a junction table for each one then?

When I want to make a report, and have it display all of the stations, for example, associated with the facility in one table cell (like the following)

Name: Facility1
Phone: 352-5532
Stations: WKPC 97.3, TKW 100.2, WRTS 103.5, etc.

...how do I make a report that would let me do that? I could link to the radio stations table from the facility table, but wouldn't it display every radio station in the box? And how would this work with junction tables? I am rather confused - any help is appreciated. Thanks!

[jzwp11]

Do you have radio stations, newspapers, and television channels in separate tables? Since all three are just types of media outlets, they should be in the same table. You can have a field in that table that distinguishes the type of media outlet.

If a media outlet can be associated with many facilities and a facility can be associated with many media outlets, they you need a junction table between the facilities table and media outlets table.

[canfish]

Jzwp,

Would I then need a separate table with the three categories for mediaoutlet types if I were to create a combo box with those options in a form for filling in new mediaoutlets? Or would I just have to let my user type them into the table and hope that they are consistent? Or... is there a way of only giving them three options of what to choose to fill in the field for a record using a form?

Thanks for the help!

canfish

[TheShabz]

I'd create another table holding the possible mediaoutlets. I'd then create a relationship between that and your junction table (enforce referential integrity). This will help avoid any issues that arise from typos and all that kind of stuff. Ideally, the goal is to have everything done with as little data entry from the user as possible.

[jzwp11]

In my original post, I did not clearly say, but I did intend for you to have a table to hold the various media outlet types. My apologies. TheShabz has explained more clearly than I did on how to structure that (thanks TheShabz).

[TheShabz]

so you would have

tblStation 1---M tblStationMedia M---1 tblMediaOutlet

tblStation holds 1 instance of each station. tblMediaOutlet holds 1 instance of each mediaoutlet type. then tblStationMedia holds the combinations. like
ID station media
1 abc radio
2 abc television
etc

[canfish]

Thanks for the help, shabz!

So, would I be correct in having the following tables?

tblOffices - tblOfficesMediaOutlets - tblMediaOutlets - tblMediaOutletTypes - tblStations, tblChannels, tblNewspapers?

If I had it that way, I would have two junction tables (italicized). Or is it more like this?

tblOffices - tblOfficesMediaOutlets - tblMediaOutlets - tblMediaOutletCodes (with an OutletTypeID to link to tblMediaOutlets)

Or are both of these interpretations wrong? It just seems weird to make separate tables for each kind of media outlet... I did that in the first place, but jzwp suggested keeping them all in one table and making a table that distinguishes the types.

Any and all help is appreciated. Thank you!

[canfish]

Also, if jzwp is right about making a table to distinguish the types, how exactly would I do that? Would it be like this?

tblMediaOutletTypes

PKMediaOutletTypeID (autonumber)
MediaOutletTypeName

tblMediaOutlets

PKMediaOutletID (autonumber)
FKMediaOutletTypeID
MediaOutletName

[jzwp11]

I assume when you say offices, you are refering to the facilities that you describe in your original post.

You do not need: tblStations, tblChannels, tblNewspapers since they are handled within the mediaoutlets table as separate types

So taking what you posted:

tblMediaOutletTypes
-PKMediaOutletTypeID (autonumber)
-MediaOutletTypeName

In the above table you will have 3 records newspaper, radio and television (you can add more as necessary of course)



tblMediaOutlets
-PKMediaOutletID (autonumber)
-FKMediaOutletTypeID foreign key to tblMediaOutletTypes
-MediaOutletName

tblOffices
-pkOfficeID primary key autonumber
-txtOfficeName

Then your junction table would relate an office to the associated media outlets

tblOfficeMediaOutlets
-pkOffMediaOutID primary key, autonumber
-fkOfficeID foreign key to tblOffices
-fkMediaOutletID foreign key to tblMediaOutlets

[canfish]

Perfect! I understand what I need to do now, I think. Marking as solved.

[jzwp11]

Please let us know if you have additional questions