New answers tagged

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.
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 &...
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;
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 ...
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 :...
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 ...
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 ...
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 ...
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 ...
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 (=...

Top 50 recent answers are included