New answers tagged database-design
0
votes
Separate tables for logging?
Yes I would use separate tables.
I worked in a company where we were doing the same for the exact same reason.
- 1,286
0
votes
Same primary key for multiple tables vs separate primary keys
Entia non sunt multiplicanda praeter necessitatem! (Occam's razor).
If all you're doing is splitting the employee table (once and only once for some/all employees), then use the original PRIMARY KEY! ...
- 28.4k
0
votes
Separate tables for logging?
When using MSSQL you can use temporal tables to track versions of your record.
See https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
- 1,733
0
votes
Accepted
Set up PL/SQL Developer
If one like to start learning SQL in general, then SQLite is even simpler than MySQL/MariaDB
It does not require any server, the "SQLite database" is a simple single file. There are some &...
- 2,592
4
votes
Set up PL/SQL Developer
SQL Developer is to Oracle Databases what Google Chrome is to the Internet.
It is not a database that you can store stuff in but, rather, a "window" through which you can look at many ...
- 7,443
1
vote
Accepted
Is good idea to split the JSONB field into a separate table in my case?
I'd say that splitting up the JSON array into a set of rows in a table is certainly a good idea. Your updates will become faster, and your queries won't become slower.
- 38.6k
2
votes
How to convert getdate() stored as varchar to date
You can use a case statement.
declare @a varchar(20)='getdate()';
select case when @a = 'getdate()' then getdate() end;
- 21.9k
2
votes
How to convert getdate() stored as varchar to date
You can use dynamic SQL for this, however it is very prone to SQL Injection.
DECLARE @a VARCHAR(20) = 'getdate()';
EXEC ('SELECT convert(datetime,' + @a + ')');
- 1,733
0
votes
Advice: Should i use multiple physical databases or one physical database and several logical ones
I wrote an article on my blog about this topic based on this question: What is most efficient design pattern for a sql database containing billions of rows per user in a single table?
The bottom line ...
- 1,286
0
votes
Advice: Should i use multiple physical databases or one physical database and several logical ones
The answer to your question depends on the business model you want for your clients.
Multitenant Database can be a real headache if some databases are heavily populated
See my old posts
Feb 11, 2019 :...
- 177k
1
vote
Accepted
Preserving foreign key properties when using Class Table Inheritance (or alternatives)
With table inheritance often there is a "type" on the root entity showing which derived entity is populated for this key. For this example it would be something like events.event_type with ...
- 24k
1
vote
Mobile finance service transaction table design
Firstly, you will need some way to know what account did that transaction.
Secondly, if you can have a transaction between accounts, you will need to do the same for the receiver account.
I think this ...
- 11
1
vote
Accepted
Solving the nested tables problem in relational databases
In addition to what the others said, to help fill some gaps:
...sometimes say the ingredients are 30 ingredients, am I going to send 30 requests to the database just to create this product?
Like ...
- 29.5k
1
vote
Solving the nested tables problem in relational databases
Is it by doing a for loop and sending 5 requests to the database
No. Backward.
Firstly, you create a row in product table. On the client side.
Then you check ingredients for it, and if some ...
- 18.4k
1
vote
Solving the nested tables problem in relational databases
? Is it the way to go for this problem?
Short answer - Yes.
How am I going to insert these rows into the database?!
Insert the product first.
Then retrieve the ID automatically assigned to that ...
- 7,443
1
vote
can date datatype varchar use index as date datatype datetime index?
The best thing to do is to change the datatype to datetime also
Short Answer - Yes!
Always store data values in fields of the correct Data Type.
It simply avoids problems just like this and makes the ...
- 7,443
0
votes
Storing Large Quantities of Time Series
If you use a time-series database, the volume of data you are describing wouldn't be considered large. Take for example QuestDB, an Apache 2.0 licensed time-series database. It can easily do what you ...
- 101
3
votes
Accepted
Exec An SQL query in a table in Database 'B' from database 'A'
Build the script from the source table using STRING_AGG and execute it using sp_executesql, qualified with the target database name. This will avoid the need for a cursor:
DECLARE @SQL nvarchar(MAX) = ...
- 25.9k
1
vote
Exec An SQL query in a table in Database 'B' from database 'A'
First thing that comes to my mind is a CURSOR and sys.sp_executesql.
Create a cursor that runs through all the creation scripts. Each time a new script is about to be executed, prefix it with the ...
- 336
3
votes
Help - Database design
In general a major concern for this type of problem is how often new fields will be added. In most places (and in my experience) new fields are added often. Business needs always change and grow, and ...
- 1,001
2
votes
Accepted
Product Object for Ecommerce in NoSQL
For design questions like these there is ALWAYS more than one answer. Also, not sure how it works on DBA Exchange but on Stack Overflow this question would get flagged as for soliciting an opinion and ...
- 136
4
votes
Help - Database design
There may be several nation checks for each field (max is 42).
Today.
Tomorrow, someone will want number 43.
It's almost guaranteed.
Database != Spreadsheet
Create a table for each of the "30&...
- 7,443
1
vote
Performance PostgreSQL 14 with long running queries causes PG POD MASTER 0 (StatefulSet) to reach MAX CORES
The shown data from pg_stat_activity does not show a long running query.
The connection's state is idle. So that connection is not doing anything and is waiting for a new command from the client (=...
- 76.1k
Top 50 recent answers are included