9 Replies Latest reply on May 11, 2011 10:31 AM by philmodjunk

    Database Design

    crtopher

      Title

      Database Design

      Post

      I want to set up a database to keep track of my kids parties - who was invited, who came, what presents were given etc.

      I'm mainly using it as an exercise in database design. I'm looking for design tips!

      I presume I would have an events table, a people table, a presents table. What would be the best way to relate these from the start.

      Thanks in advance.

        • 1. Re: Database Design
          philmodjunk

          People----<People_Event>-----Events
                               |
                               ^
                             Gifts

          People::PeopleID = People_Events::PeopleID
          Events::EventID = People_events::EventID

          People_Event::EventID = Gifts::EventID AND
          People_Event::PeopleID = Gifts::PeopleID

          You have a many to many relationship between People and events as more than one person can attend an event and more than one event can be attended by the same person. People_Event is a Join table that makes such linkage possible.

          Gifts links by both ID fields so that you can tell who gave a particular gift and at what event. You could choose to eliminate the gifts table and include the gift fields as part of People_Events. Either way, this assumes only one person is giving a given gift--reasonable for children's parties but maybe not for adults. If you wanted to handle the possibility that two or more people might pool resources to purchase a single, larger gift, you'd need an additional join table between People_Events and Gifts.

          If you haven't worked with a Join table before, here's a demo file you can download and examine that matches Contracts to Companies in a many to many relationship: 

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Database Design
            crtopher

            Hi Thanks Phil...I've set it up with the people-events join table and the appropriate portal. It's working well. I have made the gift part of the people_event table for simplicity. I haven't attempted a gift join table but if i did, would it be a join table between gifts and people or gifts and people_events. In the latter case this would be a join table involving another join table is this ok/appropriate?

            Finally, in the simpler setup, i have a peoples layout with the people_events table in a portal to show what events that person is associated with. One of the fields (called "attended?")in the join table is a simple checkbox that puts a "1" in a text field if the person attended. How do i limit the events that show up in the portal on the people layout to only show the events they attended (as indicated by the "1" field) rather than all the events they are associated with (ie invited to)

            thanks in advance

            • 3. Re: Database Design
              crtopher

              Hi Phil....scrap the second part of that...I didn't experiment fully before posting and have found portal filtering.

              Cheers, Chris

              • 4. Re: Database Design
                philmodjunk

                Come to think of it, you don't actually need another join table.

                Change the relationship between gifts and People_events to be:

                Gifts::GiftID = People_Events::GiftID

                This makes the People_Events table a "star join" between three different tables, but now several people can pool resources to give a single large gift at a given event. (Useful, if all your friends go together and buy you a car for your birthday... Surprised)

                • 5. Re: Database Design
                  crtopher

                  If only I had friends like that...!

                  First a trivial question - with the People_Events join table is it necessary to include a self-indexing auto field People_EventsID?

                  Also, going back to your original scheme, just for interests sake (so I can better understand joins and relationships) would there have been a functional difference between your set up (ie linking the peopleID and eventsID fields between the People_events table and gifts table) and one where the eventsID and peopleID fields (in the people_events join table) were linked back to the original people and events tables?

                  And finally, I gather that if i link a giftsID field between gifts and people_events tables (your last suggestion) that the field for entering gifts would appear in the portal on the events layout, the portal that contains the people_events occurrence, and would be a droplist so that for each person entered into a people_event record i would "lookup" a gift if that gift was to be given by multiple people, or i would enter a new gift as required - what concerns me is a user might lookup a previously given gift thinking they are entering a gift to be given only by the one person? How would i set this up (or am I confusing myself?)

                  thanks in advance

                  • 6. Re: Database Design
                    crtopher

                    ...would there have been a functional difference between your set up (ie linking the peopleID and eventsID fields between the People_events table and gifts table) and one where the eventsID and peopleID fields (in the people_events join table) were linked back to the original people and events tables?

                    sorry that should have been "in the gifts table" (substitute for bolded text)

                    cheers

                    chris

                    • 7. Re: Database Design
                      philmodjunk

                      Adding a primary key to your join table isn't a bad idea. Many developers add such a field to every table they create. I tend to add them on an "as needed" basis myself. Adding a primary key field after the fact is a pretty simple operation:

                      1. define the field
                      2. go to a layout for the table
                      3. Show all records
                      4. Use Replace field contents with the serial numbers option to add a serial number and also update the "next serial value" setting in the
                      5. same operation.

                       

                      Is there a functional difference? The "star join" enables multiple people giving the same gift. The previous configuration limited you to one gift record per person.

                      • 8. Re: Database Design
                        crtopher

                        Hi Phil thank you for all your help, I'm sure you're kinda busy.

                        I hope you don't mind me continuing to pepper you with questions.

                        I have it set up now so that there is a separate gift table with a Gifts::GiftID = People_Events::GiftID relationship set up, as you advised. I can enter new gifts in the people_events portal on the events layout. I also now have a gifts layout with a people_events portal displaying associated records - but how can I select pre-existing people_events records on this layout to associate them with the gift record I am looking at - i can't click into the portal since I don't want to create new records here (so i have not ticked that option in the relationship window) just create new associations - associate a giftID with a people_eventID (BTW i can go into the people_events table layout and enter gift numbers and they then show up on the relevant gift record on the gift layout but i want to be able to do it from the gift layout)

                        thanks in advance

                        • 9. Re: Database Design
                          philmodjunk

                          interesting wrinkle.

                          I think you'd need some form of scripted support. You could set up drop downs to a global field, for example where you select and event in one field and the person in the other. A script could then use the data in these two fields to find a join table record and update it with a gift ID.