4 Replies Latest reply on Sep 11, 2012 12:10 AM by crtopher

    Adding a table to existing database



      Adding a table to existing database


           I need to add a table to an existing database but can't get the relationships right to make it work properly.

           The database tracks event management for a company. The main tables comprises employees, their children, and events.

           I need to add a table for guardians - those adults who will accompany children at company events. Some will be employees (parents), but not necessarily. It is possible an adult from one family will also supervise children from another family at an event. It is also likely that a child will be accompanied by one adult at a particular event, and by another adult at a different event.

           I need to count the children attending an event, and also the adults.

           The current database uses the company's employee number to relate child and employee records and has a unique identifier for events.

           I'd appreciate some assistance in getting the relationships and field definitions right.

        • 1. Re: Adding a table to existing database

               Sounds like you have a many to many relationship. An event can be attended by many children and one child may attend many events. You could have an event-child join table, with a guardian field which gets its values from a list based on the employee/parent table. 

          • 2. Re: Adding a table to existing database

                 Database design should use worst case examples - a People that has 7 Phone Numbers - not the normal 1 or 2 Phone Numbers
                 Current focus of the database is defined by Parent Employee (containing employee data)

                 What you describe is Family Group defined by Child(ren) which relates to Parent Employee and Guardian(s)

                 Child(ren) [many] have one Parent Employee
                 Child(ren) [many] have [many] Guardian(s)

                 Events have [many] Parent Employee having one or many Children  OR
                 Events have [many] Child(ren) having one Parent Employee and many Guardians

                 Join Tables to eliminate Many  to Many relationships is the goal of Normalization.

            Home > Designing and creating databases > Creating a database > About planning a database

            About planning a database
                 A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.
                 Follow these general steps to plan a database:

            Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

            Relational Database Design 101 (part 2 of 3)

            Relational Database Design 101 (part 3 of 3)


            • 3. Re: Adding a table to existing database

                   My thinking was that the "guardian" table needs to be separate because the adults who supervise children are not necessarily related to them. Therefore it is not defined by any of the fields which connect employees or children.

              • 4. Re: Adding a table to existing database

                     You would have to decide whether the list of guardians was substantially different enough from the list of parents/employees to warrant their own table, otherwise you will be entering the same data in two different tables, a general no-no for relational database design. Even if they are substantially different, they could still belong to the same table, say "People", but have a checkbox that designates them as guardians. You would then base your value list on an appropriately related TO of the "People" table. You could do the same here...make another table occurrence (not a new table) of your parent/employee table and relate that to the join table.