Database Design - Help Appreciated
-
I want to make sure I get the basic design correct from the beginning so help really appreciated. Im creating a dbase (MySql) that will store Radio Meteor Events. Individuals collate data on a monthly basis and a contributor may collect anywhere between 4,000 to 8,000 unique events per month, we currently have 6 contributors out of a possible 30 or so. So planning ahead if all 30 contributed each month we could have around 250,00 event per month The key purpose is to correlate event capture, so did anyone else capture the same event as me Each event per contributor is unique and amongst other things each event records a full date yyyy/mm/dd and time HH:MM:SS Once all this data is in a database there would be quite general queries like how many events on a particular date across all contributors say in the month of Jan how many events across all contributors match with regard to both date and time Another common slightly more complex query would be to get all events for a particular date where the time matches with a tolerance of say 10 seconds either way so if we had Contributor 1 15:00:10 Contributor 1 15:00:30 Contributor 2 15:00:05 Contributor 2 15:01:10 Contributor 3 15:06:10 Contributor 3 15:00:10 It would return Contributor 1 15:00:10 Contributor 2 15:00:05 Contributor 3 15:00:10 so i was planning two tables 1 - Contributors 2 - Events Event ID would be unique and the primary key the link between the two tables would be the Contributor ID I’m not sure about date and time I thought for flexibility I could have Date Time Datetime The data its self is quite simple but given this could grow I want to store it in the most appropriate way to ensure that queries are efficient Help appreciated Regards John Berman
-
I want to make sure I get the basic design correct from the beginning so help really appreciated. Im creating a dbase (MySql) that will store Radio Meteor Events. Individuals collate data on a monthly basis and a contributor may collect anywhere between 4,000 to 8,000 unique events per month, we currently have 6 contributors out of a possible 30 or so. So planning ahead if all 30 contributed each month we could have around 250,00 event per month The key purpose is to correlate event capture, so did anyone else capture the same event as me Each event per contributor is unique and amongst other things each event records a full date yyyy/mm/dd and time HH:MM:SS Once all this data is in a database there would be quite general queries like how many events on a particular date across all contributors say in the month of Jan how many events across all contributors match with regard to both date and time Another common slightly more complex query would be to get all events for a particular date where the time matches with a tolerance of say 10 seconds either way so if we had Contributor 1 15:00:10 Contributor 1 15:00:30 Contributor 2 15:00:05 Contributor 2 15:01:10 Contributor 3 15:06:10 Contributor 3 15:00:10 It would return Contributor 1 15:00:10 Contributor 2 15:00:05 Contributor 3 15:00:10 so i was planning two tables 1 - Contributors 2 - Events Event ID would be unique and the primary key the link between the two tables would be the Contributor ID I’m not sure about date and time I thought for flexibility I could have Date Time Datetime The data its self is quite simple but given this could grow I want to store it in the most appropriate way to ensure that queries are efficient Help appreciated Regards John Berman
It is a very simple structure and the relationship between the table is correct. There would be no benefit to storing the date and time in separate fields. You may, in the future, want to look at creating aggregated summary tables to facilitate reporting but not for a year or more (assuming 250k per month).
Never underestimate the power of human stupidity RAH
-
It is a very simple structure and the relationship between the table is correct. There would be no benefit to storing the date and time in separate fields. You may, in the future, want to look at creating aggregated summary tables to facilitate reporting but not for a year or more (assuming 250k per month).
Never underestimate the power of human stupidity RAH
Thank You - Appreciated