Junction table or denormalize?
I’m using MariaDB.
database-design
Data lake or data warehouse first?
I have been confused whether to create a data lake or a data warehouse and hope some experienced real-world professional can give me some enlightenment.
Can one dictate the order of generated values when adding a new BIGSERIAL column to a table?
I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I’m still wondering about something. In my case, the table already has a BIGSERIAL column which is the primary key, but many rows have been deleted so now there are gaps. (The table has subsequently been fully vacuumed.) I need to regenerate the values so that they are sequential again. Here are 5 example rows of what I want to achieve where the new_value > 1000:
Trigger works for UPDATE, but for INSERT
In my database schema an organization can have multiple addresses but only one default address. I’m trying to create a trigger where if the is_default column is set to true on an insert or update, it sets the rest of the rows to false and the current one to true.
Why my simple searching query is too slow?
I don’t know why searching in my “cities” table is so slow.
My query looking for a table “cities” located about 25km from the city.
I use this simple query and the database takes almost 20 seconds to return results.
What is more efficient on select – objects identified only by their ID, or by their ID + customer id?
I have a collection of ~500K configurations. Each configuration belongs to a specific customer. There are up to a few hundreds of customers.
More columns vs. more rows to optimize storage and performance?
I have a table with this structure:
Lists and list of items instead of one table per ‘list’?
A sql database I am working on at a customer has been created with an unusual structure.
Best way to map different JSON keys to same target columns
I have CSV import data that comes from multiple vendors with 50+ different columns, and each vendor has slightly different naming.
What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?
ERD for my simple db design:
