structure/design of access mdb

  • Thread starter Thread starter Access User
  • Start date Start date
A

Access User

I'd like to run this 'design' of mine past the readers of this sub-group's...

Here's the "BIG PICTURE" - a bunch of patients (ID) had Magnetic Reasonance
Angiograms performed. Each patient had one and only one MRA performed and
that was on a day entered in MRADATE. As I understand it, each angiogram
yields an image. Each image is capable of being 'read' by a reviewer whose
initials are entered in REVINT. There were only two readers to read these
MRAs and each image was read by the pair of reviewers. If there were any
aneurisms found on an image, then the four largest ones would be described.

I have created three tables

1) tbl_MRA which records ID and MRADATE. The PK is compound ID+MRADATE

2) tbl_MRA_form which records lots of parameters but for the sake of brevity
we'll just remember that it's coding ID and REVINT which went into making a
compound PK and MRADATE which didn't

3) tbl_Aneurism which codes properties of an ANEURISM. For the sake of
simplicity we'll just stick to the fact it records ID, ANEURISM (1,....,4)
and REVINT. There is no PK.

Relationship-wise, I have a one-to-many linking ID and MRADATE spanning
tables 1 and 2. Also, there's a relationship involving ID and REVINT linking
tables 2 and 3.

I plan to have three forms. The user will open a form and the Details
section will hold the form whose record source is table 1. The Form section
will use the datasource from table 2 and also there will be a 'sub-form' in
the same section having the recordsource of table 3.

Currently, I have a somewhat different design which seems to 'work' but is
there something more canonical and in keeping with good database design I'm
missing?

Thanks for any help in advance.
 
See comments in line
--
Dave Hargis, Microsoft Access MVP


Access User said:
I'd like to run this 'design' of mine past the readers of this sub-group's...

Here's the "BIG PICTURE" - a bunch of patients (ID) had Magnetic Reasonance
Angiograms performed. Each patient had one and only one MRA performed and
that was on a day entered in MRADATE. As I understand it, each angiogram
yields an image. Each image is capable of being 'read' by a reviewer whose
initials are entered in REVINT. There were only two readers to read these
MRAs and each image was read by the pair of reviewers. If there were any
aneurisms found on an image, then the four largest ones would be described.

I have created three tables

1) tbl_MRA which records ID and MRADATE. The PK is compound ID+MRADATE
Click to expand...

This should not be a compound key. The primary key should only be the ID.
If you are going to search bu MRADATE, then create an index for it.
2) tbl_MRA_form which records lots of parameters but for the sake of brevity
we'll just remember that it's coding ID and REVINT which went into making a
compound PK and MRADATE which didn't
Click to expand...

If there can be multiple records for one record in tbl_MRA, then it should
have its own PK and the ID from tbl_MRA should be carried as a foreign key to
relate them. It there is only one record in this table for each record in
tbl_MRA, then I would suggest you don't need this table. Its fields could be
moved to tbl_MRA
3) tbl_Aneurism which codes properties of an ANEURISM. For the sake of
simplicity we'll just stick to the fact it records ID, ANEURISM (1,....,4)
and REVINT. There is no PK.
Click to expand...

It should have a PK.
Relationship-wise, I have a one-to-many linking ID and MRADATE spanning
tables 1 and 2. Also, there's a relationship involving ID and REVINT linking
tables 2 and 3.

I plan to have three forms. The user will open a form and the Details
section will hold the form whose record source is table 1. The Form section
will use the datasource from table 2 and also there will be a 'sub-form' in
the same section having the recordsource of table 3.
Click to expand...

This would indicate to me there is a one to one relationship between table 1
and table 2. In that case, table 1 and table 2 should be combined into one
table.
 
Hi,

I changed the PK from table 1 to be on ID alone. MRADATE is still on table 1
as for each ID there is only one MRADATE. In keeping with this, I removed the
MRADATE field from table 2. And, yup, there can indeed be multiple records
for one record in table 1 (they would be arising from the fact that each
REVINT has had a chance to read the film/image from the ID -- so there'd be
exactly two records per ID, one per REVINT).

On the subject of table 3's design, I created a compound PK with
ID+REVINT+ANEURISM in this same order. The relationship between tables 1 and
2 and tables 2 and 3 are one-to-many and are as before
 
Avoid compound PKs they are a real performance degrader.
If table 3 relates to table 2, then just carry table 2's PK as an FK.
 
Sorry but I thought you had said that table 3 should have a PK. It sounds
like you really know what you're talking about. Could you slow down just a
bit and explain it in long hand. If I've omitted any details, let me know so
you can help.
 
Yes, it should have a PK.
An FK (foreign key) is what is used to relate a record to its parent record.
So, if table 1 has 3 record

ID
1
2
3

And the record that has an ID of 2 has two child records and ID 3 has 2 then
you would have

ID tbl1ID
1 2
2 2
3 3
4 3

Then lets say table 3 has 2 records that relate to table 2 where the ID is 4

ID tbl2ID
1 4
2 4

See how it works?
Also, rather than a non descript field named ID, I would use something that
would easily identify the table. I also use ID at the end of a field name to
denote it is an artificial primary key that is an autonumber. Not a
requirement, just my way of reminding myself what I am dealing with
 
Must be that old brain fog or whatever, but I don't quite see....

Let me try to use your example with data that simulate what I might expect
to get entered.

for table 1 (PK = ID)

ID
1
2
3

for table 2 (PK = ID REVINT)

ID REVINT
1 A
1 B
2 A
2 B
3 A
3 B

for table 3 , PK = ID REVINT ANEURISM (just to save time and keystrokes,
I'll focus on e.g. ID #2 and imagine that REVINT A and B saw the same number
of Aneurisms that needed to get described, but they may not always)

ID REVINT ANEURISM
...... ..... .....
2 A 1
2 A 2
2 A 3
2 B 1
2 B 2
2 B 3
..... ..... ......
 
Back
Top