Relational database design

[shahram.shirazi]

Hi all,

This is a very stupid question, but it has been years since i've
touched databases so any help will be very appreciated.

I am designing a school registration database which also serves as an
analysis application for an LEA or something.

Im trying to get my head round the design and it really annoying me!!
lol

Let us say we have an LEA Employee table with a foreign key LEA_id that
links to the LEA table, which has a foreign key itself of school_id
from a school table.

If I want to have a LEA form with employee's subform so I can fill
employee details based on the LEA they are allocated to, how would I do
this? It is saying that I need to fill in the School_id, which is
correct, but I dont want it to yet.

Many thanks for helping a idiot out!

[Alan Searle]

If I want to have a LEA form with employee's subform so I can fill
employee details based on the LEA they are allocated to, how would I do
this? It is saying that I need to fill in the School_id, which is
correct, but I dont want it to yet.

If the employee is the child record of school and this is declared as a
foreign key relationship then the school_ID MUST exist in each employee
record. This is probably the error message that you are getting.

You can solve this problem quite nicely by creating a 'pseudo entry' in
the school table, maybe called 'not yet allocated'. Normally I use the
ID of '1'. Then you can set the default in the school_ID field in
employee table to '1' and then each new employee will happily be
allocated to this pseudo entry. Then in the school navigation screen
you can go to the school 'not yet allocated' and assign your employees
to schools.

Give it a try :-)

Cheers,
Alan.

[shahram.shirazi]

Hi.

Thanks for you post.

Sorry I didnt explain it well. I have people that will be related to a
specific school, such as adminstrators, teachers and student. However
LEA Employees will not be related to a school, they should only be
related to an LEA, for which a number of schools is managed by.

Regards
Shaz

[Alan Searle]

Hi Shaz,

Sorry I didnt explain it well. I have people that will be related to a
specific school, such as adminstrators, teachers and student. However
LEA Employees will not be related to a school, they should only be
related to an LEA, for which a number of schools is managed by.

This makes it a bit more tricky. I would, however, still recommend
staying with the 'pseudo entry' idea:

You could maybe have one 'pseudo-school' for each LEA with each pseudo
school being entitled 'Direct link to LEA xyz'. Then the pseudo-school
would have the same foreign key structure as normal schools thereby
linking it to a specific LEA.

In this way, you can set up full, stable relational integrity
constraints and create one set of reporting. Indeed, in the reporting,
the titles given to the 'pseudo-schools' would appear when reporting
employees linked directly to LEAs, which, in turn, would appear
alongside staff working for specific schools. This could save you a lot
of headaches!

What do you think of this idea?

I wish you well with your development and am sure it will work out.

Cheers,
Alan.

[shahram.shirazi]

Hi Alan,

If I understand you correctly you are refering to the 'default value'
attribute when designing the table in access.

This idea sounds good, but I think for a test environment only.

As I said, I havent touched DB's in years - Im actually going to
(hopefully) implement it in Oracle but as I wanted to get back into
DB's thought at having a go in access first and if possible migrating
it over.

[Alan Searle]

Hi Shaz,

If I understand you correctly you are refering to the 'default value'
attribute when designing the table in access.
No, not in this case. Here you would select a pseudo-school (i.e. a
link to the LEA) for every employee not directly allocated to a school.
These pseudo-entries would then complete the heirarchy for all staff,
even if they aren't allocated to a school. If you don't do it this way,
then you will need a lot of exception handling, at least two different
sets of reports and setting referential integrity could be tricky (or
impossible).
This idea sounds good, but I think for a test environment only.
:-/ Only test what you are planning to implement: There's no reason to
test anything which is to be thrown away.
As I said, I havent touched DB's in years - Im actually going to
(hopefully) implement it in Oracle but as I wanted to get back into
DB's thought at having a go in access first and if possible migrating
it over.

It makes no difference: A relational structure is a relational
structure. In Access, Oracle or anywhere else. Indeed, Access is the
perfect environment for testing a structure which will later be migrated
to a 'bigger' database.

But I still think you don't understand my suggestion. Here are examples
of the tables ...

LEA
ID LEA
1 LEA1
2 LEA2
3 LEA3

SCHOOL/ESTABLISHMENT
ID SCHOOL LEA (foreign key)
1 SCHOOL1 2
2 SCHOOL2 1
3 SCHOOL3 2
4 LEA1 1 (link to LEA1)
5 LEA2 2 (link to LEA2)
6 LEA3 3 (link to LEA3)

STAFF
ID STAFFMEMBER SCHOOL (foreign key)
1 TEACHER1 2
2 TEACHER2 1
3 EMPLOYEE 5 (this employee is linked to SCHOOL_ID 5 which is
really a link to LEA2)

Do you see what I mean?

If you are not comfortable with this structure, you could try putting
both Schools and LEAs in one table, giving them ESTABLISHMENT_TYPE
categories and then, where required, linking them back on each-other.
That's another way to do it.

Anyway, I wish you all the best with this.

Cheers,
Alan.

[shahram.shirazi]

Alan

Got it to work!

Yes underatand you at first but I had a think and its done.

Thank you for your helpfulness.

Best Regards,
Shaz

[shahram.shirazi]

Alan

Got it to work!

Yes I understand you at first but I had a think and its done.

Thank you for your helpfulness.

Best Regards,
Shaz