I have 2 tables which I want to establish a one-to-many relationship. My questions is on how to best setting the primary key:
Table A consists of 3 fields (a, b, c) which are combined as a primary key to make each record unique. Therefore I would set all these 3 fields with index-duplicate OK and also grouped them together as the primary key.
Table B consists of 3 fields (x, y) and an ID2 field as primary key.
1. I could add fields (a, b, c) to Table B as foreign key and link them together this way, but it seems like the keys would be large and inefficient since these 3 fields are defined as TEXT type.
or
2. I could add an stand-alone ID field as autonum index field to table A, which would be external to the primary key, and add this ID to Table B as foreign key for the linkage. Since I read somewhere that the linkage key in Table A has to be defined as a primary key, but I tried setting it up this way in Access and did not get any errors.
Any suggestions or clarifications would be much appreciated.
Table A consists of 3 fields (a, b, c) which are combined as a primary key to make each record unique. Therefore I would set all these 3 fields with index-duplicate OK and also grouped them together as the primary key.
Table B consists of 3 fields (x, y) and an ID2 field as primary key.
1. I could add fields (a, b, c) to Table B as foreign key and link them together this way, but it seems like the keys would be large and inefficient since these 3 fields are defined as TEXT type.
or
2. I could add an stand-alone ID field as autonum index field to table A, which would be external to the primary key, and add this ID to Table B as foreign key for the linkage. Since I read somewhere that the linkage key in Table A has to be defined as a primary key, but I tried setting it up this way in Access and did not get any errors.
Any suggestions or clarifications would be much appreciated.
