Access Residents Database

[Navin]

Hello, I am new to this forum, and to access, so I do not know if something like this has been posted before, if it has I am sorry.



I am constructing a database for to keep track of all the tenants in a student accommodation, I have a students table related to a tenants table via a 1 to 1 relationship. Now what I need to do is associate a tenant to a room but taking into account the academic year, i.e. 1 tenant can only be associated to one room in 2012 but to a different/same room in 2013.

Thank you very much

Navin

[recyan]

Till someone comes along :
1) Why do you have a separate Tenants table ?
2) Can the / a room accomodate more than one student at the same time ?
3) Perhaps, a junction table, StudentTenantRoom should be looked in to

Thanks

[Navin]

Well the reason students and tenants are separated is because the company promotes itself in university fairs and a lot of students sign up but not all follow on to book a room. Also all rooms are single rooms and only accomodate one person, each flat can accomodate up to a maximum of 5 tenants.

[recyan]

The way I see it on the fly currently :

tblStudents
StudentID - PK
StudentName
......


tblFlats
FlatID - PK
FlatName
....

tblFlatRooms
RoomID - PK
FlatID - FK
RoomName
.....

tblTenantsRooms
TenantRoomID - PK
StudentID - FK
RoomID - FK
OccupancyStartDate
OccupancyEndDate
....

Thanks