Multi-Table and DB normalisation

[xPaul]

Hi all.

I just wish to let you know my background first before I get into asking my questions. I started out with Access in 2009, and learnt in Office 2003. We were taught Access by creating a School database (Pupils, Tutors, Certificates, Modules). This was a part of my college course in IT. Throughout that course, I have learnt a variety of subjects like programming, web design etc. Therefore, I am pretty confident when it comes to coding etc. Though, to be honest I have not played with Access since then.

On to my questions.

I am creating a basic database, with the whole purpose of it to expand in data (obviously) and tables throughout time. It's a school database, in the sense it will be holding the information of a number of schools, not the pupils, or teachers, just the school itself. So far I have two tables.

tbl_school:

1. Education Board
2. School ID (Primary Key)
3. Type
4. School Name
5. Address
6. Telephone

tbl_passwords:

1. School ID (Primary Key)
2. School Name
3. Password 1
4. Password 2
5. Password 3

My dilemma is that tbl_school has already been populated with data, and therefor I wish to create a 1 to 1 link between two tables. As my understanding there will be 1 school to 1 school.



I do not wish to be duplicating data. Something that I don't think we went over, or I can't remember going over.


So, so far I have created the 1 to 1 link, but when enforcing referential integrity, cascade update and delete I receive an error message. I do not have any data yet in tbl_passwords, and this is why I believe I am receiving that error message.


In an ideal world, what I wish to happen is that when I type in (or import) the School ID into tbl_passwords, the School name is populated from the information in tbl_school list.


Can this be done without a query? Or do I need to decompose one of my tables further?


Thank you and best regards


Paul

 

[spikepl]

In an ideal world, what I wish to happen is that when I type in (or import) the School ID into tbl_passwords, the School name is populated from the information in tbl_school list.

And in the real world you don't. This is the point of a relational database - not to duplicate data but to find it using the relations, when needed. For display - get the data required using queries.

Ultimately - why not just add the few columns to your original table?