MySQL Forums
Forum List  »  General

Database schema
Posted by: Al Grant
Date: June 01, 2023 12:50AM

I would like some guidance please on the best way to structure my data.

I have a table of monkeys which have checks conducted on them a few times per year.

At each check a number of things sometimes happen:

1. They can have a radio transmitter attached (and removed);
2. They can have height and weight measurements taken;
3. They can have transmitter signals recorded;

The radio transmitter is the one that is causing me the most headache. Each radio transmitter only last 1 year.

Each radio transmitter has a channel number and there are only 50 channels (Ch01 - Ch50). So each year a monkey gets a new transmitter - often but but not always it is not the same channel number. Sometimes a monkey dies so then a new monkey will get a transmitter on the dead monkeys frequency.

Each monkey can only have one transmitter, and not all monkeys in the database have a transmitter.

Other things in addition to a transmitter change that can happen at a health check:

Bloods can be taken
Microchip can be inserted
Measurements can be taken

Each health check has fields for start datetime and end datetime, who did the health check and who was holding the monkey (table of users).

Note that most monkeys have only 1 microchip but occasionally they fall out, so some monkeys have two but only the most recent would routinely be displayed.

Usual queries would be:

1. Display monkey with monkey fields and related current transmitter channel, most recent microchip, transmitter attached date, weights and heights over time;
2. Display monkey with a list of recent health checks dates/times
3. List all health checks and monkey name

I have had a go at the schema but cant see how to share the image here.

Options: ReplyQuote

Written By
Database schema
June 01, 2023 12:50AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.