If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
structure/design of access mdb
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. |
#2
|
|||
|
|||
structure/design of access mdb
See comments in line
-- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#3
|
|||
|
|||
structure/design of access mdb
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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#4
|
|||
|
|||
structure/design of access mdb
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. -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#5
|
|||
|
|||
structure/design of access mdb
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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#6
|
|||
|
|||
structure/design of access mdb
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 -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#7
|
|||
|
|||
structure/design of access mdb
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 ..... ..... ...... "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
#8
|
|||
|
|||
structure/design of access mdb
That would be correct.
-- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 .... ..... ...... "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 "Klatuu" wrote: See comments in line -- Dave Hargis, Microsoft Access MVP "Access User" wrote: 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 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 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. 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. 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. 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. |
Thread Tools | |
Display Modes | |
|
|