0

I am starting a Database of multiple locations in a specific area (upwards of 8,000 unique locations). We remotely collect information routinely about these locations and data is collected over multiple collection instances throughout the year.

I have one main table (Main_Table) in the DB that stores the info that remains constant such as address, name, GPS location and the zone within which the data source is located, these data will not change. Each location is given a Primary Key which will be used as the unique identifier for the location.

Now, I have to collect data routinely on these locations like temperature, time, status (sat or unsat) and other readings, this is done for each location routinely, like 1, 2, 3 or 4 times per year, I want to store the info collected in (a) separate table(s).

I was wondering, since each location will be uniquely identified, would it make sense to create a separate table for each individual location/data source to store all the data collected over the different times of the year. So, location_one would be a table and all the data collected from location_one would be stored in that table etc. The other option I am playing with is have one other table for ALL data collected (Info_Table) and just make a new entry for each source of data with the Primary Key from Main_Table being the unique key for the data entered, which option will be faster? I am using PHP to pull and parse the data for display on a web application.

5
  • I anticipate 8,000 points to collect data from at least and routine collection will be done at least 4 or so times per year, so 32,000 entries into the database.
    – Blue_Hat
    Nov 27, 2022 at 20:42
  • Is it faster to query 8000+ tables to display the data or a single one?
    – Joe W
    Nov 27, 2022 at 21:56
  • @JoeW - With a suitable index, it is "better" to have one table, not 8000. A table with 32K rows is "tiny". Come back when you have a billion.
    – Rick James
    Nov 28, 2022 at 4:45
  • @Blue_Hat - You should probably have one table listing all the locations and another table with the readings. Maybe there will be more than the two tables, but I don't see any need for such yet.
    – Rick James
    Nov 28, 2022 at 4:47
  • Let’s be honest: this is Mickey Mouse data and the way you design it is not very important and unlikely to make any difference in performance: whether you keep everything in one table or two tables with a 1-1 relationship will make little difference. Nov 28, 2022 at 8:07

2 Answers 2

1

I anticipate 8,000 points to collect data from at least and routine collection will be done at least 4 or so times per year, so 32,000 entries into the database.

Managing 8,000 tables for the same type of object would be painful. And if you need to query across more than one table at a time, then you run into extra work with trying to create the appropriate query to do so. There would also be some slight additional overhead in searching each table and combining the data back together, multiplied for each table you need to query across at one time.

32,000 records is an extremely tiny amount of data. Even after 100 years, 3.2 million rows of data is still a small amount. An index uses a B-Tree data structure to organize the data. B-Tree's have O(log(n)) search time complexity. In the worst case, log2(32,000) = ~15 nodes that would need to be seeked through to find any subset of the data. log2(3.2 million) = ~22 nodes. Either would take the world's slowest computer less than a millisecond to run.

Moral of the story, don't prematurely optimize your database system. It already has the tools to handle large amounts of data in a performant way. Worry about optimizing performance once you actually have a performance problem eventually.

4
  • So, adding a second table and just adding the data in rows would be the best bet then? I would only be working with 2 tables and I can just select all data associated with the key that is associated with the individual point from which to collect data then?
    – Blue_Hat
    Nov 28, 2022 at 4:28
  • @Blue_Hat I think that would be most sensible, yes. General rule of thumb when starting is to design the tables to match the objects as they'll be used and consumed. Just make sure the key (or whichever columns you filter that table on) is indexed on that table, and your queries will be very fast.
    – J.D.
    Nov 28, 2022 at 14:24
  • Thanks a whole lot, this was very helpful
    – Blue_Hat
    Dec 4, 2022 at 16:51
  • @Blue_Hat No problem, glad to be of help! 🙂
    – J.D.
    Dec 4, 2022 at 17:54
-3

A table per address would be faster because the index on the foreign key in the data table would be smaller.

1
  • That depends on how you need to access the data
    – Joe W
    Nov 27, 2022 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.