https://www.red-gate.com/simple-talk/sql/database-administration/five-simple-database-design-errors-you-should-avoid/
This post have described some useful technique to avoid and reduce mistake on DB design.
It seem all the shitty design here that Wordpress have it all :) Even though you can see why WP use this way, but after all as a Web/Software developer I feel WP should not be used in complex and important site ie. payment ...
https://dba.stackexchange.com/questions/188667/best-database-and-table-design-for-billions-of-rows-of-data
https://www.quora.com/What-is-Facebooks-database-schema
https://www.facebook.com/notes/facebook-engineering/tao-the-power-of-the-graph/10151525983993920
Certainly, FB implement or any other big tech are out of normal solution, so it is for reference and research only (?)
[ https://www.quora.com/What-is-Facebooks-database-schema ]
In normal implementation we mostly use Relational DB design or noSQL.
For example User and Friend tables:
https://stackoverflow.com/questions/1009025/facebook-database-design
In my case it is Post - Location/Shop.
[ https://dba.stackexchange.com/questions/188667/best-database-and-table-design-for-billions-of-rows-of-data ]
Yeah, travel data and weather data is very interesting me. And this post mentioned it. If I have enough time I will try to build or investigate how weather app gather data for example development country like Vietnam. Weather forecast always have it's value and the longer future it predict, the more error it get ?
I often double check US Navy weather data with some other source to compare. You know storm direction is a critical information.
1. Don't use NoSQL for this. The data is highly structured and fits a relational database perfectly.
2. I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a PostgreSQL instance when I started my job and it couldn't handle the volume of data as it was on a small AWS instance.
3. I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
4. I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.
My function does not use datetime much, so I can ignore these complex part.
Yes, my data fit for Relational DB, but I am not sure chose new table or new column. Since my Post table always have place/location data then it seem never have null cell. So new column will be acceptable.
And by my experience new column better new table in some case like reduce join, object relation mapping ...
My location data combined of many level administrative devisions.
This one is very similar with my problem
https://stackoverflow.com/questions/8763771/is-this-a-good-db-schema-for-locations
I want to store post in a shop and want to know the location of the shop to country / province|city and prefecture | district;
https://github.com/Vheissu/Open-Source-Database-Schemas/blob/master/vheissu-instagram-schema.md
Instagram schema above did not store explicitly location ID (ie. PlaceID). So how it use lat/long only to fetch location ?
It seem not what I need but may be this way better since geolocation / placeid can be change and depend on 3rd partly ie. Google, 4sq, mapbox ... ? And cling to PlaceID seem not alway correct ?
Anyway, my app have placeID and some mechanism for update this over Google.
Some other shit may be helpful :)
https://www.8bitmen.com/instagram-architecture-how-does-it-store-search-billions-of-images/
This post have described some useful technique to avoid and reduce mistake on DB design.
It seem all the shitty design here that Wordpress have it all :) Even though you can see why WP use this way, but after all as a Web/Software developer I feel WP should not be used in complex and important site ie. payment ...
https://dba.stackexchange.com/questions/188667/best-database-and-table-design-for-billions-of-rows-of-data
https://www.quora.com/What-is-Facebooks-database-schema
https://www.facebook.com/notes/facebook-engineering/tao-the-power-of-the-graph/10151525983993920
Certainly, FB implement or any other big tech are out of normal solution, so it is for reference and research only (?)
[ https://www.quora.com/What-is-Facebooks-database-schema ]
In normal implementation we mostly use Relational DB design or noSQL.
For example User and Friend tables:
https://stackoverflow.com/questions/1009025/facebook-database-design
In my case it is Post - Location/Shop.
[ https://dba.stackexchange.com/questions/188667/best-database-and-table-design-for-billions-of-rows-of-data ]
Yeah, travel data and weather data is very interesting me. And this post mentioned it. If I have enough time I will try to build or investigate how weather app gather data for example development country like Vietnam. Weather forecast always have it's value and the longer future it predict, the more error it get ?
I often double check US Navy weather data with some other source to compare. You know storm direction is a critical information.
1. Don't use NoSQL for this. The data is highly structured and fits a relational database perfectly.
2. I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a PostgreSQL instance when I started my job and it couldn't handle the volume of data as it was on a small AWS instance.
3. I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
4. I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.
My function does not use datetime much, so I can ignore these complex part.
Yes, my data fit for Relational DB, but I am not sure chose new table or new column. Since my Post table always have place/location data then it seem never have null cell. So new column will be acceptable.
And by my experience new column better new table in some case like reduce join, object relation mapping ...
My location data combined of many level administrative devisions.
This one is very similar with my problem
https://stackoverflow.com/questions/8763771/is-this-a-good-db-schema-for-locations
I want to store post in a shop and want to know the location of the shop to country / province|city and prefecture | district;
https://github.com/Vheissu/Open-Source-Database-Schemas/blob/master/vheissu-instagram-schema.md
Instagram schema above did not store explicitly location ID (ie. PlaceID). So how it use lat/long only to fetch location ?
It seem not what I need but may be this way better since geolocation / placeid can be change and depend on 3rd partly ie. Google, 4sq, mapbox ... ? And cling to PlaceID seem not alway correct ?
Anyway, my app have placeID and some mechanism for update this over Google.
Some other shit may be helpful :)
https://www.8bitmen.com/instagram-architecture-how-does-it-store-search-billions-of-images/
Comments
Post a Comment