Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18

    Multi-Tier Design

    I'm not sure if multi-tier design is the appropriate terminology, but here's an example of what I'm trying to accomplish.

    I'm building a database to track information about a children's television show.
    At the top-most level, will be general information that is specific to that episode (air date, episode title, episode name, etc...).
    I have a table for that.

    At the next level, will be repeatable bits of information about that particular episode (a welcoming scene, a craft, a game, etc...).
    I started out with a table of generic scenes, but many episodes will have scenes that are only specific to one episode and I thought that table would become unnecessary.
    I was already going to require a one-to-many table to join the episode to the scenes, so I just used a normal text field for the scene name instead of referencing another table (I can run a distinct query for a dropdown list of choices or they can free-type).

    At the next level, under scenes, would be the actors in scene.
    I have a table of actor names an a one-to-many table to join the scene to the actor (as one actor may be in multiple scenes and we need to be able to track who was in what).

    At the next level, under actors, would be the characters that the actors played.
    I had a table of character names, as a single actor could be multiple characters and some characters require multiple actors or can be played by different actors (puppets, for example).
    Like the scene table, I removed it and just added a text field for what character was played by what actor in scene-actor table.

    Each episode has a music scene and each music scene has multiple songs.
    I have a table with a listing of songs and a one-to-many table joining the songs to that music scene.


    We also need to track who performed during that music scene, so they are also listed as actors and their respective positions (bass player, lead singer, etc...) as characters.

    Here is a generic visualization of what I'm talking about...

    Code:
    Episode
        |
        ——Scene
                   |
                   ——Actor
                              |
                              ——Character
                   ——Song
                              |
                              ——Actor
                                         |
                                         ——Character
    As mentioned, I removed the characters table in favor of just putting that information in the scene-actor join table, but it seems like it really should be it's own dataset.
    That would require another level of join table and this is where it's starting to get foggy.
    I haven't been able to wrap my head around how to only show that character that was played by that actor in that scene of that episode (and even moreso, who was the drummer for that song in that scene of that episode).
    To make matters more annoying, while the band members don't change song-to-song, the lead singer can be different for different songs in the same set.
    Instead of just being able to determine the actors for the music portion, it seems like I would have to define them for each song (or, at least, the lead singer).

    I've been talking about a top-level, table, design, but the same will hold true for queries and forms (especially, data-entry forms).
    I'm trying to think about what will become too time-consuming for someone to add information.
    They would have to see a form for the general episode information, then click a button that opens another form to enter a scene, then click another button to open another form to enter the the actors for the scene, then click another button to open another form to choose their character.
    It just seems unnecessarily complicated, but it may just be the nature of the beast.

    Hopefully, I'm just missing something that will declutter the whole thing and make life easier for everyone.
    Thanks...
    JJ

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are dealing with concepts here, but you are also dealing with details. I, and perhaps others, am/are not familiar with your terms scene, characters, actors, song..
    Can a scene have multiple songs, multiple singers/characters etc?????

    Is this database for 1 TV show or should there be a TV SHOW/SERIES at the highest level of your hierarchy?
    I think it would be helpful to you and readers if you would create some mock data and build a model for your design that could be tested. See stump the model.

  3. #3
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    I'll happily give complete context.
    Would screenshots of design and data be sufficient or should I upload the actual database?
    The information contained would not be considered private.
    JJ

  4. #4
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    The episodes, themselves, can be viewed at https://www.flcbranson.org/php/mlmMe...&languageID=EN.
    Each episode is part of a series (more of a topical series, not disparate series like Friends, Seinfeld, etc...).
    JJ

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you don't understand db normalization, that is first. Seems like you do, though. Second would be sketching out table relationships, and I mean that literally. Many developers will use whiteboards, large paper sheets or similar to write out table names and list fields and their characteristics (required, integer, etc.). It if oft said that if you can't put it on paper you can't expect to jump in, start building tables and expect complete success. That can be done during or after vetting the model as described by the link given. If you're the sole developer/user of the db, you probably have to rely on your own knowledge of the process that the db needs to support.

    One comment made has me wondering about relationships/joins:
    used a normal text field
    That I would not do under most circumstances. If you need the table, best to link via autonumber primary key in parent table to long integer as the foreign key in the child table. The child table should also have its own autonumber PK.

    It seems that your initial struggle will be deciding which things are entities (tables) and which are attributes (fields) of the entity and those decisions are not always easy to make, even when you think you understand the nature of a business. You can always get as far as building table relationships and posting a pic here, but be prepared to get a lot of questions posed by those trying to gain an idea of how things relate to one another.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 4 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Micron View Post
    If you don't understand db normalization, that is first. Seems like you do, though. Second would be sketching out table relationships, and I mean that literally. Many developers will use whiteboards, large paper sheets or similar to write out table names and list fields and their characteristics (required, integer, etc.). It if oft said that if you can't put it on paper you can't expect to jump in, start building tables and expect complete success. That can be done during or after vetting the model as described by the link given. If you're the sole developer/user of the db, you probably have to rely on your own knowledge of the process that the db needs to support.
    I'm not a DBA, by any stretch, but I do have some knowledge.
    I have built multiple, somewhat large (1000s of records, 10s of tables/queries/forms), databases between Access, MS-SQL, and MySQL.
    Some of them are relational.

    Quote Originally Posted by Micron View Post
    One comment made has me wondering about relationships/joins: That I would not do under most circumstances. If you need the table, best to link via autonumber primary key in parent table to long integer as the foreign key in the child table. The child table should also have its own autonumber PK.
    This is where I stated that I was going to do individual tables for scene names, but I changed it as the majority would be one-of-a-kind instances.
    I didn't want to dedicate a table for that kind of data.
    It's easier to just use a distinct query to populate a drop-down for a text field that would also allow the user to just type a new value in.
    Maybe that's not the right way of doing it, but it seems logical to me.

    Quote Originally Posted by Micron View Post
    It seems that your initial struggle will be deciding which things are entities (tables) and which are attributes (fields) of the entity and those decisions are not always easy to make, even when you think you understand the nature of a business. You can always get as far as building table relationships and posting a pic here, but be prepared to get a lot of questions posed by those trying to gain an idea of how things relate to one another.
    Here are my table relationships.

    Click image for larger version. 

Name:	Screenshot (10).jpg 
Views:	35 
Size:	65.2 KB 
ID:	46582

    I am here to gain assistance, knowledge, and best practices.
    I will endeavor to be as much help, to you, as I can, so I can get as much help, from you, as I can.
    Hehehe...
    JJ

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have a screen capture program that allows you to grab just a screen portion, maybe use that and trim all the excess space? Or crop your posted image? I imagine it's a combination of your and my screen res that makes the font so small that I can't read it at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Hopefully, this is better.
    I do have a 2160p screen, so there is extra space.
    JJ

    Click image for larger version. 

Name:	Capture.jpg 
Views:	28 
Size:	100.4 KB 
ID:	46585

  10. #10
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    One thing that I think I'll need to change is adding another level of ActorID/CharacterID to TblJoinEpisodesceneSong, so that I can track who the lead singer on a per-song basis was.
    JJ

  11. #11
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Here is a complete dataset for one episode...

    Code:
    EpisodeID EpisodeDate SeriesName EpisodeSeriesPart EpisodeName SceneName ActorName CharacterName SongName
    1 10/31/2021 Enlarge My Heart 1 Welcome Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 Welcome Kelsey Punzo Captain Tyler's Assistant
    1 10/31/2021 Enlarge My Heart 1 Worship Tatiana Cheshire Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Gina Di Lecce Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Chrissy Guevara Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Ellary Punzo Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Mike Sutherland Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Mark Pack Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Angie Kelly Nothing Is Impossible
    1 10/31/2021 Enlarge My Heart 1 Worship Tatiana Cheshire I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Gina Di Lecce I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Chrissy Guevara I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Ellary Punzo I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Mike Sutherland I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Mark Pack I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Angie Kelly I Shall Not Be Moved
    1 10/31/2021 Enlarge My Heart 1 Worship Tatiana Cheshire By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Gina Di Lecce By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Chrissy Guevara By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Ellary Punzo By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Mike Sutherland By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Mark Pack By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Angie Kelly By Faith
    1 10/31/2021 Enlarge My Heart 1 Worship Tatiana Cheshire My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Gina Di Lecce My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Chrissy Guevara My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Ellary Punzo My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Mike Sutherland My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Mark Pack My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Angie Kelly My God Is So Big
    1 10/31/2021 Enlarge My Heart 1 Worship Tatiana Cheshire I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Gina Di Lecce I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Chrissy Guevara I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Ellary Punzo I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Mike Sutherland I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Mark Pack I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 Worship Angie Kelly I Open My Heart To The Love Of God
    1 10/31/2021 Enlarge My Heart 1 TT & CT Josh Hart Tom Teller
    1 10/31/2021 Enlarge My Heart 1 TT & CT Nathan Irwin Dr. Kno
    1 10/31/2021 Enlarge My Heart 1 TT & CT Hannah Hunter
    1 10/31/2021 Enlarge My Heart 1 TT & CT Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 TT & CT Kelsey Punzo Captain Tyler's Assistant
    1 10/31/2021 Enlarge My Heart 1 Confessions Austin Ebersol
    1 10/31/2021 Enlarge My Heart 1 Confessions April Ebersol
    1 10/31/2021 Enlarge My Heart 1 Offering William Hunter
    1 10/31/2021 Enlarge My Heart 1 Key Insight Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 Action Point Heather Waller
    1 10/31/2021 Enlarge My Heart 1 Muscleman John Michael Gentry Muscleman
    1 10/31/2021 Enlarge My Heart 1 Information Station Josh Hart Tom Teller
    1 10/31/2021 Enlarge My Heart 1 Information Station Nathan Irwin Dr. Kno
    1 10/31/2021 Enlarge My Heart 1 Abraham Matt Curtis Abraham
    1 10/31/2021 Enlarge My Heart 1 Abraham Doug Bagenstos God
    1 10/31/2021 Enlarge My Heart 1 Information Station Josh Hart Tom Teller
    1 10/31/2021 Enlarge My Heart 1 Information Station Nathan Irwin Dr. Kno
    1 10/31/2021 Enlarge My Heart 1 Muscleman John Michael Gentry Muscleman
    1 10/31/2021 Enlarge My Heart 1 Lucy & CT Velvet Dougharty Lucy
    1 10/31/2021 Enlarge My Heart 1 Lucy & CT Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 Song Spot Austin Ebersol Read Your Bible And Pray Everyday
    1 10/31/2021 Enlarge My Heart 1 Song Spot April Ebersol Read Your Bible And Pray Everyday
    1 10/31/2021 Enlarge My Heart 1 TT & CT Josh Hart Tom Teller
    1 10/31/2021 Enlarge My Heart 1 TT & CT Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 God Is Big Kelsey Punzo Captain Tyler's Assistant
    1 10/31/2021 Enlarge My Heart 1 Snack Heather Waller
    1 10/31/2021 Enlarge My Heart 1 TT & CT Josh Hart Tom Teller
    1 10/31/2021 Enlarge My Heart 1 TT & CT Tyler Punzo Captain Tyler
    1 10/31/2021 Enlarge My Heart 1 Farm Hands Isaiah Herbert Marcus
    1 10/31/2021 Enlarge My Heart 1 Farm Hands William Hunter Wilber
    1 10/31/2021 Enlarge My Heart 1 Altar Call April Ebersol
    1 10/31/2021 Enlarge My Heart 1 Wrap-Up Tyler Punzo Captain Tyler

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Can you upload your actual database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    I don't see an upload option, here, but here's a link.
    https://1drv.ms/u/s!AlRf6b1u0N2Pv9U5...Pt67A?e=zM2fEE
    JJ

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's in the top menu bar. Here is its hyperlink
    https://www.accessforums.net/showthread.php?t=70301

    Many will not download from external sites, so you will probably get more responses by posting here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    It would seem like the Advanced editor should be the default.
    Hehehe...

    VirtualChurch.zip

    Hopefully, this makes things easier.
    JJ

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. 3 Tier Queries - Consolidate to One?
    By bs0d in forum Access
    Replies: 18
    Last Post: 03-03-2015, 06:41 PM
  2. Managing Contacts in a multi-tier database
    By MSAccessNewGirl in forum Forms
    Replies: 7
    Last Post: 02-27-2014, 12:08 AM
  3. Replies: 6
    Last Post: 05-11-2012, 11:16 AM
  4. Multi-User Design
    By tyrobrio in forum Access
    Replies: 3
    Last Post: 04-30-2012, 08:17 AM
  5. 3RD Tier Combo Box
    By Jademonkey2k in forum Access
    Replies: 4
    Last Post: 11-15-2010, 08:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums