We are currently using a summary table that aggregates information for our users on an hourly basis in UTC time. The problem we are having is that this table is becoming too large and slowing our system down immensely. We have done all the tuning techniques recommended for PostgreSQL and we are still experien…
Tag: database-design
PostgreSQL Index Usage Analysis
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the “profiler” tool for SQLServer, but I’m not aware of a similar tool included with Postgres. Answer I …
How to store a list in a column of a database table
So, per Mehrdad’s answer to a related question, I get it that a “proper” database table column doesn’t store a list. Rather, you should create another table that effectively holds the elements of said list and then link to it directly or through a junction table. However, the type of l…
Guid Primary /Foreign Key dilemma SQL Server
I am faced with the dilemma of changing my primary keys from int identities to Guid. I’ll put my problem straight up. It’s a typical Retail management app, with POS and back office functionality. Has …
How to delete from a table where ID is in a list of IDs?
if I have a list of IDs (1,4,6,7) and a db table where I want to delete all records where ID is in this list, what is the way to do that? Answer Your question almost spells the SQL for this:
How to Set Customer Table with Multiple Phone Numbers? – Relational Database Design
A customer can have multiple phone numbers e.g. Cell, Work, etc. phoneID in Customer table is unique and points to PhoneID in Phone table. If customer record is deleted, phoneID in Phone table should also be deleted. Do you have any concerns on my design? Is this designed properly? My problem is phoneID in Cu…
Is there a severe performance hit for using Foreign Keys in SQL Server?
I’m trying my best to persuade my boss into letting us use foreign keys in our databases – so far without luck. He claims it costs a significant amount of performance, and says we’ll just have jobs to cleanup the invalid references now and then. Obviously this doesn’t work in practice,…
Database design for monitoring status of applications’ functionalities
I’m creating the database for monitoring status of applications’ functionalities. The logic is following: Each application has its own, specific list of functionalities that I’m monitoring. Each …
Which is the most common ID type in SQL Server databases, and which is better?
Is it better to use a Guid (UniqueIdentifier) as your Primary/Surrogate Key column, or a serialized “identity” integer column; and why is it better? Under which circumstances would you choose one over …
Database design, which table has the foreign key
I have an Table USER (USER_ID, PASSWORD, NAME, …) and an Table ACCESS_ROLES for the Users, every user can have one ACCESS_ROLE (ONE-TO-ONE). Which table has the Foreign Key? I would put the USER_ID into ACCESS_ROLES table. Is there any best practice approach? Answer Since you will be having a one-to-one…