New answers tagged database-design
0
votes
How to convert data separated pipe to rows?
The problem here is that you have violated 1NF by storing multiple values in a single tuple. You are going to have to parse this into rows. You can use the built in STRING_SPLITfor this.
select *
from ...
- 101
-1
votes
Logging Which Columns Have Been Read Or Written To
Not exactly what you are looking for but might help:
Have a look at Brent Ozars sp_BlitzIndex:
exec sp_BlitzIndex
@DatabaseName='AdventureWorks2012',
@mode=2;
GO
It will show you date and ...
- 31
0
votes
Creating new table for new fields
There's three ways I can think of that you can go about this, in order of preference:
You have your application run the appropriate SQL scripts to alter the corresponding tables to add the new fields ...
- 24.3k
1
vote
How to deal with invoices that reference products that don't exist any more?
1: What is the best way to store data of a product in invoice? Is it by bringing the id of the product, or by bringing all info related to the product? Actually is it a good practice to store id of ...
- 2,880
0
votes
one table is related to two other tables is that a good idea?
It is ok. Doctrine calls this approach Inheritance Mapping.
https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/inheritance-mapping.html
- 422
1
vote
Accepted
Get the difference for a column value by all its previous rows data
Something like this, may be?
sum(case transaction_type when 'debit' then -1 else 1 end * amount)
over (partition by vault_id order by id rows unbounded preceding and current row)
- 21.2k
2
votes
Accepted
Junction table or denormalize?
A junction table isn't going to affect your performance here, especially with a process that only runs at a frequency of every hour.
NoSQL doesn't buy you any advantage here either, and IMO should ...
- 24.3k
1
vote
Accepted
Data lake or data warehouse first?
There's a lot of similar and overlapping terms these days (Data Lake, Data Swamp, Data Warehouse, etc) that I wouldn't get too hung up over, IMO.
Data Lakes are informal places to centralize different ...
- 24.3k
1
vote
Accepted
Marking/retrieving a long array of numbers as "true/false" in postgres
Both, bit and bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value. ...
- 70.7k
10
votes
Accepted
Do datatypes of columns affect query performance?
Yes. And probably a lot.
You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and discouraged. Related:
Would ...
- 151k
1
vote
The implications of lack on indexes on supporting a large production database
As J.D. has mentioned indexes (or lack of) can have a significant impact on query level performance and it certainly warrants further investigation. In a nutshell the more that SQL can efficiently use ...
- 31
2
votes
The implications of lack on indexes on supporting a large production database
Typically it's a good idea to index on the fields in the predicates (JOIN, WHERE, and HAVING clauses) of the queries commonly executed on your server or the queries that require executing with the ...
- 24.3k
2
votes
Foreign key refencing multiple primary keys
A FOREIGN KEY constraint can only ever reference a single target table by design.
Maybe partitioning or inheritance fits your use case?
If not, here are possible workarounds:
Without master table
You ...
- 151k
2
votes
Accepted
Azure Database Performance Throttling - Use Replication or?
Depending on which flavour of Azure SQL is being used you may be able to implement Resource Governor or equivalent.
This allows you to allocate a portion of the hardware to each workload. The large ...
- 22.8k
Top 50 recent answers are included