Need help creating relationships

[Jag-Stang]

Hello,

 

I'm trying to build a database for months now and I still can't make it work.

 

Here is the description:

 

There are two different dimensions:

1. Funds, Subfunds, Sections, Subsections, Series and Subseries: these are categories through which the documents have to be hierarchically organized.

A document will always belong to a Fund, but it might not have a Section, only a Serie, for example, but it always follows an hierarchy:

Fund -----> Subfund -----> Section -----> Document

Fund -----> Section -----> Document

Fund -----> Section -----> Serie -----> Subserie -----> Document

 

 

2. Installation Unit, Compound Document, Simple Document and Digital Representation: are ways through which the documents can present themselves.

Digital Representation: a MP3; a JPEG; a MOV file.

Simple Document: The CD where the MP3's where digitized from; the photo album where the JPEG's where scanned from; the cassete where the MOV's where rendered from. Map from Paris. Louvre Museum Ticket.

Compound Document: a collection of Simple Documents - "The Beatles Discography": collection of 5 CD's; "France 2014": photo album of holidays in Paris and other cities; "France 2014": video8 cassete from the holidays.

Instalation Unit: the guy went to France, took the pictures, recorded movies and listened to the Beatles Discography and decided to put it all in one box with the map and the ticket. This box is a mix of the latter categories.

 

These are some examples of how these documents can be organized:

 

EXAMPLE 1

Fund "John Doe"

     -----> Section "Audio"

          -----> Subsection "CD's"

               -----> Serie "Rock"

                    -----> Subserie "The Beatles"

                         -----> Simple Document "Beatles CD 1"

                              -----> Digital Representation "Help.mp3"

                              -----> Digital Representation "Lucy in the Sky with Diamonds.mp3"

                          -----> Simple Document "Beatles CD 2" 

                              -----> Digital Representation "Yellow Submarine.mp3"

                              -----> Digital Representation "When I'm 64.mp3"

 

EXAMPLE 2

 

Fund "John Doe" 

----->Subfund "John Doc INC. - Culture Enterprises"

     -----> Section "Shows produced by John Doe"

-----> Subfund "John Doe's Personal Life"

     -----> Section "Holidays"

          NO SUBSECTION HERE

               -----> Serie "Europe" 

                    -----> Subserie "Paris 2014" 

                         -----> Installation Unit "A box with stuff from John Doe's Holidays to Paris 2014" 

 

OR FINALLY - IF THE GUY WHO'S WORKING IN THIS FUND HAD THE TIME TO DIGITIZE THE STUFF FROM THIS LAST EXAMPLE 2

                  

EXAMPLE 3

Fund "John Doe" 

----->Subfund "John Doc INC. - Culture Enterprises"

     -----> Section "Shows produced by John Doe"

-----> Subfund "John Doe's Personal Life"

     -----> Section "Holidays"

          NO SUBSECTION HERE

               -----> Serie "Europe" 

                    -----> Subserie "Paris 2014" 

                         -----> Installation Unit "A box with stuff from John Doe's Holidays to Paris 2014" 

                              -----> Compound Document "The Beatles Discography"

                                   -----> Simple Document "Beatles CD 1" 

                                        -----> Digital Representation "Help.mp3"

                                        -----> Digital Representation "Lucy in the Sky with Diamonds.mp3"

                                   -----> Simple Document "Beatles CD 2" 

                                        -----> Digital Representation "Yellow Submarine.mp3"

                                        -----> Digital Representation "When I'm 64.mp3"

                              -----> Simple Document "Map from Paris" (leveled as a Compound Document because it's inside a box - Installation Unit)

                              -----> Simple Document "Louvre Museum Ticket" (leveled as a Compound Document because it's inside a box - Installation Unit)

 

I just can't make this work and becoming desperate.

 

Can you help me?

 

Thank you!!

 

[comment]

A couple of things that could use clarification:

 

1. Is this structure rigid:

 

InstallationUnits -< CompoundDocuments -< SimpleDocuments -< DigitalRepresentations

 

By "rigid" I mean will a Digital Representation always belong to exactly one Simple Document, a Simple Document to a Compound Document and a Compound Document to an Installation Unit? Specifically, I find this part confusing:

 

leveled as a Compound Document because it's inside a box

 

 

2. What attributes (fields) will you need to describe each of the mentioned entities (Fund, Subfund, Section, Subsection, Series, Subseries, Installation Unit, Compound Document, Simple Document, Digital Representation)?

 

 

3. How is this going to be used? IOW, what is the purpose of hoarding all this information?

[Matthew F]

Rather than building a complicated folder structure, sometimes its just better to add attributes or labels to your base document.  For example, Fund, Section, Serie, Subserie could all be fields on your monetary document records.   One way to decide whether a parent table (e.g. Fund) is neccessary depends on whether there are important attributes r.e. each Fund that you want to make a note of on a specific Fund record, that are independent of, or universal across the individual document records belonging to that Fund.

 

As Comment was alluding, if the relationships do not hold true in each case then you should again try to not overbuild the relationship structure, but instead use label or attribute fields on the document records.   You can create summary parts on your layouts, and search and sort by attribute fields so you don't need a parent table just to display your results in an organized manner.

 

If you take a look at the iTunes folder structure, it is not as complicated as your music organization chart.  Should you should be using something similar? ( Music >-- Group >-- Album >-- Songs )

[Jag-Stang]

Hello! Thanks for your time!

 

1.

A Digital Representation is basically, for instance, 12 tracks of a CD. Combined they form the Simple Document that's the record of the CD.

A Composed Document is always the combination of several Simple Documents.

An Instalation Unit it always the combination of both: it can have Simple Documents (like CD's from several bands) and a CD Collection that can't be sold separately). Or it can have have lots of random pictures (Simple Documents) mixed in with photo albums (Composed Documents).

 

2. Every single one of them has exactly the same fields (dates, descriptions, history, context, participants, etc.)

 

3. This is for a collection of very old stuff that belonged to musicians that might one day turn into a Museum (but in Portugal, in what Culture is concerned, we may wait for the rest of our lives. Even Filemaker is something weird around these place... sad...) So these are the rules of the archives that I have to follow.

 

3.1. A Fund may have (or not) a Subfund; A Section, a subsection; A Serie, a Subserie. 

BUT A Fund might not have Sections and move directly into Series.

All of these may have Instalations Units, Composed Documents or Simple Documents (the latter, may or not have Digital Representations).

 

Thank you very much!!!

[Matthew F]

 

This is for a collection of very old stuff that belonged to musicians that might one day turn into a Museum (but in Portugal, in what Culture is concerned, we may wait for the rest of our lives

Wow.  I hope you will have a system in place for storing backups!   Keeping the structure simple will also help if the day comes around when someone has to export this all to some other form of database or storage system. 

 

 

A Fund might not have Sections and move directly into Series.

It sounds like Sections and Series should probably be attribute fields, but not necessarily tables.  I'm not even sure that your "Installation Unit" requires its own table.   What happens when some of the items are taken out of the installation unit and moved to another unit, or sold, etc?  Will the contents of the Installation unit change, or should it be immutable from the time that it is first generated?  It sounds like you may need keep a record of transactions, like you would in a bank account, in order to know what was present in the archive at a given date in the past.

 

 

these are the rules of the archives that I have to follow

Does this mean that the museum already has a record keeping system? Is it electronic or on paper?  If it is in flat files (like Excel) or in log books, I can't imagine that it has anything close to the structural complexity that you outlined in your first post.  Perhaps most of those items should remain as simple descriptors, like columns on an Excel sheet, and thus be represented by fields on your document table.  Following the museum's rules should be easy if you create a field for each one of those attributes.  Keeping your solution simple, closer to a flat file, will make it easier to import and export data when the need arises. 

 

Because you have a top down hierarchy (a series of one-to-many relationships), setting up a lot of related tables may not be so helpful.  In contrast, if your are dealing with many-to-many relationships, setting up the related table structure would help you quickly find and display related information.