A star schema really lies at the intersection of the relational model of data and the dimensional model of data. It’s really a way of starting with a dimensional model, and mapping it into SQL tables that somewhat resemble the SQL tables you get if you start from a relational model. I say somewhat resemble … Read more
I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well. We have 2 tables notification —————– id (pk) userid notification_type (for complexity like notifications for pictures, videos, apps … Read more
There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than … Read more
Is there a way to store that sub-select as a pseudo-column in the table? A VIEW like has been advised is a perfectly valid solution. Go for it. But another way fits your question even more closely: a “computed field” or “generated column”. STORED generated columns have been added with Postgres 11. See: Computed / … Read more
I don’t agree with all these posts saying 30 columns smells like bad code. If you’ve never worked on a system that had an entity that had 30+ legitimate attributes, then you probably don’t have much experience. The answer provided by HLGEM is actually the best one of the bunch. I particularly like his question … Read more
You’ll wish you had used separate databases: If you ever want to grant permissions to the databases themselves to clients or superusers. If you ever want to restore just one client’s database without affecting the data of the others. If there are regulatory concerns governing your data and data breaches, and you belatedly discover that … Read more
There is a good paper on MySQL geolocation performance here. EDIT Pretty sure this is using fixed radius. Also I am not 100% certain the algorithm for calculating distance is the most advanced (i.e. it’ll “drill” through Earth). What’s significant is that the algorithm is cheap to give you a ball park limit on the … Read more
Linq to SQL using a table per class solution: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/01/linq-to-sql-inheritance.aspx Other solutions (such as my favorite, LLBLGen) allow other models. Personally, I like the single table solution with a discriminator column, but that is probably because we often query across the inheritance hierarchy and thus see it as the normal query, whereas querying a specific … Read more
Bear in mind that the answers you receive will be subjective. With that disclaimer out of the way, this is how I would go about setting up such a system. TL;DR: Use a currency rates table to store currency rates for different currencies and dates when they are applicable. Store amounts in both the local … Read more
CHECK constraints cannot currently reference other tables. The manual: Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. One way is to use a trigger like demonstrated by @Wolph. A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the … Read more