Table design question for metadata

[Alex_Ma]

Looking for help in designing my tables. In my case I have unique records e.g. people but the metadata associated with each record/person is based on effective and termination date. In other words, for a single record/person

Person A, smoked from 1/1/2000 to 12/31/2001. Chewed gum from 5/1/2000 to current.

I was originally going to create 2 tables for each set of metadata (smoking and gum chewing) but there doesn’t appear to be a way to link the primary field of the gum chewing table to the smoking table.

So I guess what I’m asking is; from a design standpoint should all the metadata be in a single table with effective and termination dates for each attribute (smoking and gum chewing) or should they be in 2 separate tables?

TYIA

[Tim_Wilson]

Is it just smoking and gum chewing that you’d like to track, or are there other metadata you will want to include eventually? Will there be unique attributes you’d like to track for gum chewing and smoking?

If it’s just a start data and end date, then I think two tables is the way to go. If you anticipate having different attributes for your gun chewing and smoking records, then I would go for separate tables for each.

[Alex_Ma]

Tim_Wilson:

i

Right now it’s only that metadata. I’m not sure how I’d want to design it.

For the table, I’m thinking gum chewing would only have entries if they did it a particular day like
Person A - Gum Chewing - 10/4/2022
Person A - Gum Chewing - 10/5/2022
Person B - Gum Chewing - 10/4/2022

For smoking the table would be effective and term dates
Person A - Smoking - 10/1/2022 - 10/3/2022
Person B - Smoking - 10/4/2022 - 10/5/2022

[Alex_Ma]

So I guess the question now is, how can I create a linked record to the smoking or gum chewing table to the Person table? Is it just to create the first column as an Autonumber type then set the subsequent field/column as a Linked Record? Suggestions are welcome. TYIA

[Tim_Wilson]

From what you’ve said, it certainly seems like tracking gum chewing and smoking are two different things and deserving of separate tables. To link them, just create a new Link to another record column in your Person table and choose one of the two tables. Repeat for the other one. You’ll be able to pull data and generate views from there.