Combine Multiple Tables (1 Viewer)

Chrisss

New member
Local time
Today, 13:43
Joined
Nov 25, 2020
Messages
2
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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:43
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

You have two options. Either use one table with potential null fields, due to non-applicable attributes for each asset, or you can look up the EAV (entity-attribute-value) approach.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:43
Joined
Jul 9, 2003
Messages
16,245
How about having a main form with the basic asset details and a subform displaying the attributes something like attached:-
 

Attachments

  • Combine Multiple Tables_1a.zip
    28.4 KB · Views: 109

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:43
Joined
Jul 9, 2003
Messages
16,245
subform displaying the attributes something like attached:-
One of the main bugbears with this approach is that in this simple form the operator needs to select each of the attributes from a combobox. However, this can be done automatically by using my checklist code which is explained on my website here:-


Along with several examples, one in particular which would be applicable in your case..
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2002
Messages
42,976
I would use an Asset table to hold the name and other common characteristics along with a type code and then three sub tables. One for each type. All linking would be via the Asset table so projects are linked to assets, not the individual tables. You have to go though the Asset table to get to the details if you need them.
 

Users who are viewing this thread

Top Bottom