Proper database modeling for many-to-many relationship between records

Question

all I building a small database application and I could use some input on the best approach to create a relationship between my tables.

I think I have it figured out but I've been staring at it so long I need a second set of eyes and opinions.

This model is for a race car driver. Where I am stuck in setting the relationship is on the Division table. Basically a driver can be in multiple divisions. To account for this I have added a DriverDivision table which is basically a lookup that allows me to map a driver to multiple divisions.

You can see from the model off of the driver there is a PointsStanding and a RaceResults table as well. This are associated to the driver as 1:M. Where I am stumbling is I'll need to also filter results and standings by Division. Do I need to create lookup tables between Division and these two tables as well? Looking at the model I can get all the results through the DriverInfo page but I don't know if that is the right approach? Navigating this would would always return a full object so I could see all results as well as the drivers associated to it. Am I way off track or or is there a better model/pattern to follow? When I first started this model I thought the driver would be the primary point but it feels like Division may be. A driver has to be associated to a division, points and results are associated to the driver but I'll need to be able to view all points and all standings by division.

I'd appreciate any suggestions or advice.

-Cheers

Answer 1

As I understand your post, a racer rides in one of more races, and each race belongs to a division.

One question is, can a driver belong to a division but not participate in any races in that division? Depending on the answer, you might not need a relation between Driver and Division. A relation between RaceResult and Divison makes more sense.

So you'd get something like:

Driver -->> RaceResult <<-- Race
                       <<-- Division

It seems to me like you could calculate standing based on past RaceResults. If that's true, I would not store standings in the database at all. Storing a result that you could also calculate is bound to result in inconsistencies: for example, if some updates the race result, but not the standing.

Finally, your table structure seems very generic for an initial design. I'd try to simplify it to the bare-bones information storage you need for the first version of your application.

Answer 2

first, what is DriverDivisionId for? I’m assuming your using it as a primary key for the combo of division/driver, but you can use both the DivisionId and DriverId as the primary key for this.

second, you can add the divisionId to the point standings and race results as well, then use both the driverId and divisionId to get the results for a specific division/racer combo

edit:

after thinking for a minute I think I would add one more table, a 'Race' table that will be a list of all races. There you would need a 'RaceId' to identify the race. Then you can store all the information about this race there (including division), and in the RaceResults table you would have your RaceId, DriverId, PositionFinished, PositionStarted, RacerPoints