My wife and I have a Ministry and I’m trying to create a basic DAM (Digital Asset Management) db. I used to do a great deal of db work but haven’t touched it in 6 years and it is amazing how rusty I am.
I need to track 3 basic types of Assets: Video, Audio only, Documents; each Asset Type has very different characteristics. And these Assets may be combined in different ways into what I call Projects. A one Project may have 15 different Assets combining several Video, Audio and Document Assets. Another Project may only have a single Document and no Video or Audio. The Assets may be reused in several different Projects.
So, Projects have many Assets and Assets have many Projects. I don’t have any trouble with the Assets for Projects join that is a simple many-to-many table. My trouble is trying to get the 3 different Asset Types unified into a single table. I’ve worked for days and tried just can’t figure this out, any help is appreciated.
tblVideoAsset -> VidAsstID_PK, ResolutionID_FK, FrameRateID_FK, CodecID_FK & others
tblAudioAsset -> AudAsstID_PK, BitRateID_FK, FormatID_FK, SampleRateID_FK, LanguageID_FK & others
tblDocumentAsset -> DocID_PK, WordCount, Synopsis, LanguageID_FK
tblAssets -> AssetID_PK, AssetTitle, AssetDate, ????
Among many other things, I tried creating a tblTYpe -> TypeID_PK, Type (Audio, VIdeo, Document) then
tblAsset-Type -> TypeID_FK, Asset_ID and tried joining each of the various Asset tables to it with Type but that didn't work
tblProject -> PrjID_PK, PrjName, PrjDate
tblAssets4Projects -> PrjID_FK, AssetID_FK (creating a compound PK)
I need to track 3 basic types of Assets: Video, Audio only, Documents; each Asset Type has very different characteristics. And these Assets may be combined in different ways into what I call Projects. A one Project may have 15 different Assets combining several Video, Audio and Document Assets. Another Project may only have a single Document and no Video or Audio. The Assets may be reused in several different Projects.
So, Projects have many Assets and Assets have many Projects. I don’t have any trouble with the Assets for Projects join that is a simple many-to-many table. My trouble is trying to get the 3 different Asset Types unified into a single table. I’ve worked for days and tried just can’t figure this out, any help is appreciated.
tblVideoAsset -> VidAsstID_PK, ResolutionID_FK, FrameRateID_FK, CodecID_FK & others
tblAudioAsset -> AudAsstID_PK, BitRateID_FK, FormatID_FK, SampleRateID_FK, LanguageID_FK & others
tblDocumentAsset -> DocID_PK, WordCount, Synopsis, LanguageID_FK
tblAssets -> AssetID_PK, AssetTitle, AssetDate, ????
Among many other things, I tried creating a tblTYpe -> TypeID_PK, Type (Audio, VIdeo, Document) then
tblAsset-Type -> TypeID_FK, Asset_ID and tried joining each of the various Asset tables to it with Type but that didn't work
tblProject -> PrjID_PK, PrjName, PrjDate
tblAssets4Projects -> PrjID_FK, AssetID_FK (creating a compound PK)