Correct way to build tables?

[MattAnderson]

Hi Guys I'm having a bit of a problem understanding how i should create and link these tables. I'll explain it as clear as possible.

Basically I have a table for Students at a school which has their "user number" "first name" "last name".

I also have a table which stores information on tablets and has fields such as "serial number" "model" "insurance policy number" "total value"

Now currently every student owns a tablet and i have this information in one whole table but obviously it would be best to separate it so there is a table for students and a table for devices but still keeping that link between which student owns which device.

I will also need to make sure that come a years time these same students will be able to have new devices as well as keep the details from their previous devices.

I hope i explained this clearly enough.

Thanks in advanced,

Matt

 

[Uncle Gizmo]

You might find this post a good start:-
http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[KenHigg]

Sounds like you need a field in the student table (an FK) that links to the tablet tables PK - ? This should work if you only have one tablet person student, which I assume you will...

 

[MattAnderson]

Cheers I will have a look at that link in a bit and let you know how i get on, @KenHigg eventually one student will have two tablets?

 

[KenHigg]

Then you will need to have a third table that links student to tablet(s)

 

[MattAnderson]

A third table being for the second load of tablets I'm assuming. The thing I can't understand is how I make that relation of which students have which unique device when they're in separate tables.

Thanks,

Matt

 

[KenHigg]

No the third table would not be set/used like that. Let me cobble together a sample schema...

edit:

tblStudents
id - pk
name
etc

tblTablets
id - pk
model
etc

tblStudentTablets
id - pk
student_id - fk
tablet_id - fk

So you have a list of stundents and a list of tablets. Then you create a tblStudentTablets record where the student_id is chosen/linked to the students table pk and the tablet_id is chosen/linked to the tablets table...

edit 2:
You could make it work by having a second 'tablet' field in the students table but it would not be scalable...

 

[Uncle Gizmo]

An interesting question you raise. You want to link a device to a student, once that device is paired with a student then it will not be available for any other student.

However you want to record that pairing so when the device is handed to another student you still retain the information that there was a previous student that had the device in their care.

I think this breaks down into a temporal problem. Or if you like who has or who had the device when.

Now in your table associating the device with a student you would also have a start date and end date when a student has or had possession of the device.

For a student that has possession of the device you would expect to see a start date and end date that encompassed the current date.

Now you would write code that would inspect the dates and could report " that device is currently issued" or " that device is due to be returned on date x"

Alternatively a combobox selection for the device could be filled with a set of records which excluded devices currently with students. One benefit of this method is that it would be self adjusting.

 

[MattAnderson]

@Uncle Gizmo I think I explained it slightly wrong. The students will keep that same tablet for 3 years but some may get a new one after one or two. In this case then there would need to be a record of both of their devices attached to their name

 

[KenHigg]

Could a tablet ever come back and be assigned to a different student?

 

[MattAnderson]

No they will stay with that specific student forever