Database Design and Relationships

[JP]

Sorry for the repost, I inadvertantly posted to the wrong forum the first time.
------------------------

Hello,

I am making a simple football schedule database which includes:

Team
-TeamId (primary key)
-TeamName

Schedule
-ScheduleId (primary key and autonumber)
-HomeTeamId
-AwayTeamId
-GameDate

I have created a one-to-many relationship between the TeamId and HomeTeamId
in the Relationships view and had to add the Team table (Team_1) again so I
could create a one-to-many relationship between the TeamId (in Team_1) and
AwayTeamId.

After saving the relationships I see a "+" next to all the TeamIds in the
Team table. But when I click on it I only see the ScheduleId and the
GameDate. I was expecting to see the HomeTeamId and AwayTeamId too. Is
that because I have my TeamId referencing two different fields in the
Schedule table? Should I be structuring this differently or is this just a
limitation in Access?

Thanks,

 

[tina]

here's my response to your earlier post in the gettingstarted ng:

you set up the relationships in the Relationships window correctly (very
good). your problem is that you're expecting to use the table itself as a
"good" user interface for data entry - very bad. tables are not intended for
data entry in Access; that's what forms are for. recommend you turn OFF the
subdatasheet property in each of your tables (see table Design view,
Properties box from the toolbar or menu bar), then save and close and you're
done with those tables. create one or more forms for the data entry; i can't
suggest a setup with the info you posted, because the form(s) design should
be based on what setup will best facilitate your data entry.

hth

 

[Jamie Collins]

I am making a simple football schedule database which includes:

Team
-TeamId (primary key)
-TeamName

Schedule
-ScheduleId (primary key and autonumber)
-HomeTeamId
-AwayTeamId
-GameDate

I have created a one-to-many relationship between the TeamId and HomeTeamId
in the Relationships view and had to add the Team table (Team_1) again so I
could create a one-to-many relationship between the TeamId (in Team_1) and
AwayTeamId.

A design principle (not my invention) is to make things as simple as
possible but no simpler.

In my land, a team's name is it's key. Also, during the course of a
single season, each team is scheduled to play all others in the same
league _exactly twice_, one home and one away. In your Schedule, teams
may play themselves, play an unlimited number of games simultaneously,
etc. Your PRIMARY KEY on an autonumber column would be inadequate
here.

Jamie.

--