Audio Archive: a Q? of structure

[tim trudgen]

Hi all,

I work for a NGO as a Parttime, scratch that, overtime IT person. We are building a radio station for isolated people in Arnhemland austrlia. We have to create and store everything that goes to air thus the need for an Archive for tracking the audio. so heres the problem I'll try to make it brief.

I have 3 main tables "oringinal Masters", "production Masters" & "Final masters".

I have to track which source audio (original master OM)which have been used in any particular production master (PM) and then link these originals to the final produced piece (final master FM).

But here is the trick, both the OMs and the finals have a unique ID serial number. However, there is the potential for thousnad of PMs to be produced each year and they are expendable and must be deleted from the archive when the finalmaster is completed. Thus I cannot use a unquie serial number which is maintained in the database evevtable for the PMs. instead I am using a 4 digit auto calculated ID.

I have made 3 sets to attemtp to do this

"production masters"
="OM_PM tracking" one ot many

"final masters"
-"PM_FM tracking" one to one

"OM_PM tracking"
="PM_FM tracking"

and I figure that I will create a script to copy the apropriate fields from the above set to a table "OM_FM tracking" everytime a new entry is created.

BUT, THERE MUST BE A SIMPLER WAY.

I'm new to databases so I won't pretend that I really know what I'm doing.

Any Ideas.

Tim.

[whanigsberg]

RE: Audio Archive: a Q? of structure

Tim:

As a general suggustion you might consider using linking tables.

I think you already have OM table each record of which has a unique OM_id and a PM table with a PM_id field. A linking table called OM_PM would at a minimum contain the OM_id and PM_id fields. (PM_id could be your four-digit autogenerated number.)

This wuld allow you to create a set comprising OM linked 1:N to OM_PM which is linked 1:1 to PM.

This approach would make it simple to know which PM records are linked to a particular OM.

What I do not know is whether this approach is compatible with your operational environment.

Bill

[tim trudgen]

RE: Audio Archive: a Q? of structure


Yeah that works for linking OM and PM files("OM_PM tracking" is a linking table OM_PM like you described).

But the real problem lies in linking OM and FM records without relying on the PM_id in the long term. As PM records must be deleted. I also don't want to keep the PM_IDs. This may be just pedantic, but I am uncomfortable with the redundancey of the PM_ID once the a PM records is no longer needed

I am thinking of just creating a OM_PM_FM linking table containing only field for their respective ID numers. I can then use this to create a OM_FM linking table. The OM_PM_FM would then be free to be filtered at intervals.

[Ohlen Cartmell]

RE: Audio Archive: a Q? of structure

"I am thinking of just creating a OM_PM_FM linking table containing only field for their respective ID numers. I can then use this to create a OM_FM linking table. The OM_PM_FM would then be free to be filtered at intervals."

This seems to me to be the way to go. You'd have one set linking OM=PM=FM in one to many links. You would use this set to create the FM's. You would delete the PM using a form based on the PM's.

You would have another set OM=FM you'd use to continue tracking.