92, 000 posts??? but I am only new
- Thread starter shazty
- Start date
phew.. I was asking round the office for some donations:
W have deisgned a database which consists of 6 tables, one picked as the master results table and all the others attached by one-to-one relationships with ref int and cascades switched on for all. However we can enter data to all fields in one table but when we click on another related table it comes up with the following message:
"you cannot add or change a record becuase a related field is required in table "results".
The results table just happens to be the master table from which all other relationships join to, with the join going from the primary key of the results table to the primary key of the other table, in all case the primary key being the "patient ID",
You may ask why so many tables, well we couldnt fit all the data into one table so we split it up into groups
W have deisgned a database which consists of 6 tables, one picked as the master results table and all the others attached by one-to-one relationships with ref int and cascades switched on for all. However we can enter data to all fields in one table but when we click on another related table it comes up with the following message:
"you cannot add or change a record becuase a related field is required in table "results".
The results table just happens to be the master table from which all other relationships join to, with the join going from the primary key of the results table to the primary key of the other table, in all case the primary key being the "patient ID",
You may ask why so many tables, well we couldnt fit all the data into one table so we split it up into groups
I think it has something to do with the fact that we have the primary key in every table set to patient ID and this is also the field we wish to link to all the other tables. Do I have to changed the indx to read yes duplicates OK and assign the primary key to another autonumber field?
Every table has its own primary key already set as patient ID. This is the same for every table as this is the identifying field for each table which has to be thte same, are you saying we need to have a differenet field set as the primary field for each table. They are already set as no duplicates?
If we change the primary key to a different field in each table, how can will the information for a specific patient ID put into the results table cascase to all the other tables if the Patient ID is not the primary key in all the other tables?
I don't really understand
I don't really understand
A primary key is used as a unique identifier for a record. Each table should have its OWN PK. The field that links the record to a record in another table is called the FOREIGN KEY. PatientID should be the PK in only ONE table that is the table with the main patient info. if you want to have separate tables for additional info for organization and security, that's fine. You can setup a one to one relation by using PatientID as your foreign key with a unique index and using an autonumber or some other value as your primary key.
You will not always have a one to one relation. For example, you might have a table that records each patient visit. Patients can have multiple visits. So there would be multiple records per patientID.
I am a bit concerned about what you mean by the results table cascading to the other tables. That's the purpose of the FK, to relate the child tables to the parent table. But there should be no other data from the parent table in the child tables other than the patientID as the FK.
You will not always have a one to one relation. For example, you might have a table that records each patient visit. Patients can have multiple visits. So there would be multiple records per patientID.
I am a bit concerned about what you mean by the results table cascading to the other tables. That's the purpose of the FK, to relate the child tables to the parent table. But there should be no other data from the parent table in the child tables other than the patientID as the FK.
ScottGem said:
But the patient ID has to be put into all the tables becuase how else will we know the data in a particular table is for a specific patient if the information is not duplicated from the results master table?
Again, the PatientID IS in the child tables but as a FOREIGN KEY not the Primary Key. But that's the ONLY value necessary from the related table. One to One relations are not that common. Usually you only need one table. However, I can see a situation where you might to use separate tables for confidentiality. I.E. a technician enters test results without knowing the name of the patient, only their ID. So the technician only has access to the table for the results they need to record. However, in that case, The technican still inputs the PatientID as a foreign key into the record.
Scott,
Thanks for your advice,
This is how it is laid out
Table one:Results, identified by patient ID, there will only be one set of results for each patient there are 150 fields in this field
Table two: medical history, identified by patients Id, there will only be one set of values for each patient ID about 50 fields for this table
Table three: serologies, identified by patient ID, there will only be one set of results for their serologies about 50 fields for this table
Table four: donor, identified by patient ID, only one set of info for their donor about 70 fields
Table five: transplant history identified by patient ID, only one set of results will ever go here and there are about 100 fields in this.
and so on...
As you can see there will never be a second set of results for any patient and due to the massive size of this database we cannot physically put it all in one table access simply does not allow us to do this.
This is why we have split each section into separate tables and tried to relate them through the patient ID.
There are NO duplicate fields in any of the tables, but if we type in the patient ID into the master table we want that ID to show in all the other tables without having to go and type it in separately to each one,
Thanks guy's I will try Scott's suggestion and let you know how I get on
S
Thanks for your advice,
This is how it is laid out
Table one:Results, identified by patient ID, there will only be one set of results for each patient there are 150 fields in this field
Table two: medical history, identified by patients Id, there will only be one set of values for each patient ID about 50 fields for this table
Table three: serologies, identified by patient ID, there will only be one set of results for their serologies about 50 fields for this table
Table four: donor, identified by patient ID, only one set of info for their donor about 70 fields
Table five: transplant history identified by patient ID, only one set of results will ever go here and there are about 100 fields in this.
and so on...
As you can see there will never be a second set of results for any patient and due to the massive size of this database we cannot physically put it all in one table access simply does not allow us to do this.
This is why we have split each section into separate tables and tried to relate them through the patient ID.
There are NO duplicate fields in any of the tables, but if we type in the patient ID into the master table we want that ID to show in all the other tables without having to go and type it in separately to each one,
Thanks guy's I will try Scott's suggestion and let you know how I get on
S
I strongly suspect you have a normalization issue here. In my experience table with a large number of fields are not normalized properly. I suspect you probably have a lot of repeating groups and should have several 1 to many relations instead of 1 to 1.
If you want to post examples of some of your table or a copy of your database without real data, we can advise further.
If you want to post examples of some of your table or a copy of your database without real data, we can advise further.
