Need help with my NBA database

[lboski]

Hey guys



I'm currently doing a NBA database for a class project. I have encountered a problem for which I cannot find a solution:

First of all, sorry for the database being in spanish, hope it's not a problem. The problem is, as you can see, I have a table called "Partidos" (games), in which ID_Partido is the key (autonumber); ID_Jornada (gameday 1,2,3...), ID_Stadio (stadium 1,2,3...), ID_Equipo1,ID_Equipo 2(team 1, team 2, 1,2,3...) are key in their respective tables. What I want to do is to make sure no team plays more than 1 game per gameday (and obviously not against themselves). I have tried with the tables you can see on the right side of the image, but haven't been able to find a solution.

Any help will be greatly appreciated, cheers.

I firstly put the question in the wrong section, sorry.

[InsuranceGuy]

Is the requirement to maintain this constraint as part of the data model? If so, you need to further normalize Partidos. I'm not aware of a model-level approach which works when you have two different fields for team identification in the same table [Equipo 1] and [Equipo 2].

[June7]

Those additional tables don't really make sense.

Might be able to prevent duplicate team/date combinations by setting compound index in Partidos. You would need two. ID_Jornada with Equipo1 and ID_Jornada with Equipo2. Otherwise will need VBA code to validate data.

Preventing a team from being matched with itself will probably require VBA code to validate data entry. If team matches are selected by comboboxes, comboboxes can be manipulated with code so that a team selected in one combobox is not available for selection in other combobox. Can even exclude a team if they have already been matched for given date.

With code, can usually accomplish anything. The more customization and 'user friendly', the more code.


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

[lboski]

OK thanks, I'll see what I can get. Thanks for the help!

[InsuranceGuy]

Ah, but June, how does this recommendation address preventing the same team from being listed in two different games on the same date, but one under the Team1 field and once under the Team2 field?

cheers
jeff

[June7]

Compound index that includes the game date and Team1 fields will not permit duplicate combination. Same for an index composed of game date and Team2 fields.

[InsuranceGuy]

If I'm understanding you correctly, you would take Games table and create two unique indexes, which inherently creates unique constraints:

Index1: (DateID and Team1)
Index2: (DateID and Team2)

If we assume that Team1 is a Home team and Team 2 is an Away team, that means that any team is prohibited from playing two Home games on the same date nor playing two Away games on the same date. We agree on this point completely. Here's the problem. Those indexes will not prevent me from doing this:

Game, GameDate, Team1, Team2
1, 11/8/2015, Lakers, Bulls
2, 11/8/2015, Bulls, Lakers

Thus we are able to violate the principle that teams may not be listed multiple times on the same date. If data access is restricted to forms, to achieve this a person could add some validation using a preferential combination of VBA, SQL, or domain functions. That doesn't appear to be the intent.



If the requirement is that the model needs to prevent teams from playing more than one game per day, then the only way I'm aware of achieve this objective using the data model is to add a relationship table that includes a unique constraint on Date and Team combinations.

Something like a table tblGameTeamRelationship:

Game (FK), GameDate(FK), Team(FK), Away_Home
1, 11/8/2015, Lakers, Away
1, 11/8/2015, Bulls, Home

On tblGameTeamRelationship, the developer should assign a unique constraint to (GameDate and Team). With such a constraint the model prevents a team from playing two Home games on the same date, ensuring we meet the capabilities of your approach. It also goes further and rejects any attempt to add an Away game on the same date as a Home game.

[lboski]

Still haven't been able to solve the problem...

[June7]

Did you try InsuranceGuy's suggestion?