One primary key in two tables

Question

I am first timer with ms access and also here

I am working on a database of library. I have two different tables which stores details one for books and others for Periodicals.

How can make accession number primary key for both tables.

Are autonumber a accession number when a new entry made in books are periodicals.

I have two different tables which stores details one for books and others for Periodicals. Different names do not mean different entities. It must be one table. — Akina, Apr 14, 2019 at 19:08
Do you want a single primary key across the 2 tables? I don't think Access can do this by default, and I'd recommend against working around that. — Greg, Apr 14, 2019 at 23:28

David Spillett · Accepted Answer · 2019-04-15 00:06:09Z

The two options would be:

Model them in the same table with a property to distinguish their type (such fields are often called a discriminator) and all the properties of both types.
Model them with a third table holding their common properties (and usually a discriminator also) and one child table per type. This pattern is referred to as table inheritance.

The latter option would be preferable, but I'm not sure how one would implement it in Access (I've not used that in a decade and a half).

1 Answer 1