10 Replies Latest reply on Jun 3, 2010 9:51 AM by comment_1

    Review my Database schema

    rjett

      Title

      Review my Database schema

      Post

      I'm new to database theory and Filemaker in general. I've been asked to come up with a Vans database for our company to track all our vans, maintenance, drivers, and any accidents. Before I actually dive in, I wanted to make sure I had a solid relational model. Here's what I have:

       

      Table 1: Vans                                               Table 2: Maintenance                   Table 3: Drivers                           Table 4: Accidents

      Fields: van_id (prime key)                         Fields: maintenance_id (PK)       Fields: driver_id (PK)                  Fields: accident_id (PK)

                    make                                                             mileage                                            driver_name                                 damage

                   model                                                             work_done                                       license_expiration                      liability_amt

                    year                                                                cost                                                                                                           van_id(FK)

                   purchase_date                                             driver_id (FK)                                                                                          driver_id(FK)

                   purchase_amount                                       van_id (FK)

                   license_plate                                                comments

                   vin_num

                   registration_expiration_date

                   insurance_expiration_date

                   driver_id (FK)

       

      I'm not sure if all this makes sense. I'm assuming that I'll just link up the primary keys to the foreign keys in the other tables in the relationships interface of filemaker. Since I'm new at this, any guidance would be appreciated.

        • 1. Re: Review my Database schema
          comment_1

          It looks very reasonable. I would question one point though: you have the driver assigned to a van in the Vans table. This means each van has only one driver at all times. If that were true, you would not need the driver's ID in the Maintenance and Accidents tables.

           

          The way you have it now, you can tell who's driving a van now, and who was driving it at the time of a maintenance or accident. If it is important to know who drove a van on a particular day 6 month ago, then you should add a table for this (with fields for van_id, driver_id and start_date) and take driver_id out of the Vans table.

           

           

          BTW, the Maintenance and Accidents tables could probably use a date field.

          • 2. Re: Review my Database schema
            rjett

            Ok so a couple questions:  Each van does not have a unique driver so I took driver_id out of the vans table. I also made a drivers_of_vans table. Should this table have a drivers_of_vans_id prime key? If so, should that be used as the foreign key in tables like the maintenance and accident tables in place of driver_id?

            • 3. Re: Review my Database schema
              comment_1

               


              rjett wrote:

              Should this table have a drivers_of_vans_id prime key?


              It's good practice for every table to have a auto-entered serial number ID field - even if it's not being used (yet).

               

               

               


              rjett wrote:

              If so, should that be used as the foreign key in tables like the maintenance and accident tables in place of driver_id?


              Well, theoretically, if you know the van ID and date of the maintenance/accident, you should be able to tell who's been driving it - without requiring anything else. In practice, I'd say fill out the driver's ID anyway.

               



              • 4. Re: Review my Database schema
                rjett

                So to clarify, here's what my relationships look like:

                 

                Does this look kosher?

                • 5. Re: Review my Database schema
                  comment_1

                  1. The end_date field in Drivers_of_vans is redundant because a new record implicitly revokes all previous assignments with the same van_id.

                  2. The arrangement looks good "on paper"; the question is whether you can sustain it in practice in terms of workflow and user interface. When reporting on an accident, how will the drivers_of_vans_id field in Accidents get populated?

                  • 6. Re: Review my Database schema
                    rjett

                    Regarding your second point, my logic was that when an accident occured, I would just enter in the drivers_of_vans_id and pull in the relavent van_id and driver_id through that. Is that a dicey way of doing things?

                    • 7. Re: Review my Database schema
                      rjett

                      Also regarding your first point, I put the end date in so that I could look back historically to see who had driven a particular van during a certain time period. With my current setup, I'm assuming that I create a brand new drivers_of_vans record each time someone switches the van they're driving?

                      • 8. Re: Review my Database schema
                        comment_1

                         


                        rjett wrote:

                        when an accident occured, I would just enter in the drivers_of_vans_id


                        But how will you know the correct drivers_of_vans_id to enter?

                         


                        • 9. Re: Review my Database schema
                          rjett

                          I assumed I could just go into that table and find who I needed to? Is a better way to do that just to use vans_id and drivers_id as foreign keys in that table?

                          • 10. Re: Review my Database schema
                            comment_1

                             

                             


                            rjett wrote:

                            I assumed I could just go into that table and find who I needed to? Is a better way to do that just to use vans_id and drivers_id as foreign keys in that table?


                            It depends on what's convenient to the user.

                             

                             

                             



                            rjett wrote:

                            I'm assuming that I create a brand new drivers_of_vans record each time someone switches the van they're driving?


                            Yes.