New answers tagged

1 vote

MySQL design and best design practices

My advice is to not do it at all but buy a 3rd party SaaS option such as Auth0 (no affiliation). You get the benefit of being able to federate with corporate identity providers (if that's your market) ...
LoztInSpace's user avatar
0 votes

How to migrate 2 different Cassandra databases into one cluster

To migrate the data there are several options available, depending on the volume of data. For smaller datasets, data can be migrated via CSV using either: cqlsh COPY TO (export) and COPY FROM (import)...
Mário Tavares's user avatar
0 votes

database design for a checklist app (units, inspections sheets, inspection questions)

Any database structure where each "item" ends up as a column will be a nightmare. The reason is the database structure is rather rigid and also married to your code. Even worse if you use ...
bobflux's user avatar
  • 1,391
0 votes

database design for a checklist app (units, inspections sheets, inspection questions)

So, to summarize, you have units, general inspection sheets (questions) and answers for specific inspection sheets and specific units. There is a many-to-many relationship between inspection sheets ...
A. Darwin's user avatar
  • 111
0 votes

Relationship from Orders to Shop and Supplier, or to general User?

Relationship tables (such as User_Shop) are only needed in many-to-many (M:N) relationships, so you don't need User_Shop, Shop_Supplier,... The User-Supplier relationship is interesting. Does a record ...
A. Darwin's user avatar
  • 111
1 vote
Accepted

Need help on database design for 5 entities complex relations

Looking at your diagram, I think your difficulty starts where you mistakenly try to model "Languages" as a subordinate of "Lines", when it is actually an independent entity. Apart ...
Giannis Tsimarakis's user avatar
-4 votes

General approach for creating updated records with identical primary keys while preserving existing records

create table existing_table (id int primary key, val data) create table custom_table (id int primary key, val data) select e.id, case c.id when null then e.val else c.val end as val from ...
SergeyA's user avatar
  • 433
1 vote

Need help on database design for 5 entities complex relations

In my opinion, your task is simple enough to write code that works 'identically' in both MariaDB and MySQL. There are two "things" to think about: Entity -- Implement as a table with ...
Rick James's user avatar
  • 77.6k
0 votes

Parallel Structures in database: One table with type field or multiple nearly identical tables

It depends. As you note, both solutions have tradeoffs and you must pick one better suited to specifics of your case. I am leaning towards the solution A ("Make a Apple_Basket, Orange_Basket and ...
Yano_of_Queenscastle's user avatar
0 votes

Parallel Structures in database: One table with type field or multiple nearly identical tables

Plan B, of course. If the only difference between the "baskets" is a type of items in the basket, then you can have just a "dictionary" for them. If the items are really different (...
White Owl's user avatar
  • 727
0 votes

3NF Decomposition and NP-Completeness

You understood it the other way around. The decision problem to the "is a schema in Third Normal Form?" question is NP-Complete, but given a schema you can find a Third Normal Form ...
Daniel Salmun's user avatar
0 votes

Should we repeat multilevel dependency reference columns to avoid multiple joins breaking the normal forms for better performance

It it still worth to follow the normal forms in that case also if I have say 9 child parent tables? Unless you are actually encountering the performance problems, I would recommend keeping things ...
Yano_of_Queenscastle's user avatar
0 votes

How do I design a table which has limited key diversity?

When modelling data in Cassandra, the primary objective is to design tables which exactly match the application queries. This means that you need to start the modelling process by listing all the ...
Erick Ramirez's user avatar
1 vote
Accepted

Referencing information from same table in Postgresql

It's a tree, so let's build an example tree with 10 leaves per level and 7 levels, so about 1.1M rows. -- create raw data CREATE UNLOGGED TABLE tree1 ( id INTEGER NOT NULL GENERATED BY ...
bobflux's user avatar
  • 1,391
-1 votes

Get the best performance out of postgres

The problem needs to be analyzed in detail, perhaps you should see if the SQL really takes up so much CPU (use explain (analyse,verbose,buffers) your sql, or is it affected by other sql)?
sheng yan's user avatar
1 vote

Referencing information from same table in Postgresql

it seems my only 2 options are: recursive CTE JOIN on the same table. If it's a fixed number of joins and a small number, then for simplicity's sake, I'd say go with option #2 and do a few self-...
J.D.'s user avatar
  • 37.1k
1 vote

Choosing a Database system

If I understand your question correctly about how to handle a multi-tenant database, then it does depend, like most things database related. But in particular, it depends on how many tenants you plan ...
J.D.'s user avatar
  • 37.1k
0 votes

Choosing a Database system

whether to store all business data in a single table or create multiple databases for each business Different servers. Joining data from different businesses in the different databases of the same ...
White Owl's user avatar
  • 727
1 vote

How to calculate MAX server memory and MIN server memory?

Below powershell dbatools commands gets and sets automatically the instance max memory to the recommended, it uses calculations from Jonathan Kehayias's post about SQL Server Max memory # Calculates ...
S.D.'s user avatar
  • 746
2 votes
Accepted

Optimizing Negative Int/BigInt Ranges in SQL Server: Strategies and Benefits

It doubles the range of values that can be used, but I would recommend seeding with 1 rather than -2,147,483,648 etc. If you run out of identity values starting with -2,147,483,648 this is a bigger ...
David Wiseman's user avatar
0 votes

MongoDB schema for visit and like

Go with just MySQL. CREATE TABLE Liked ( user_id ..., product_id ..., PRIMARY KEY(user_id, product_id), INDEX(product_id, user_id) ) ENGINE=InnoDB; CREATE TABLE Visited ( user_id ....
Rick James's user avatar
  • 77.6k
2 votes

How do I build a data model for a grid of XY data consisting of many to many relationships?

The Data Storing the same combination of participants twice is a serious normalization error and should not be considered as an option. The data that is unique is in one of the corresponding ...
Giannis Tsimarakis's user avatar
1 vote
Accepted

How do I build a data model for a grid of XY data consisting of many to many relationships?

A "many-to-many" relationship is typically modelled using a "junction" (or "link") table with foreign keys pointing to the respective entities. In your case you will have ...
mustaccio's user avatar
  • 25.6k
1 vote

SQL Server using table's Primary Key as part of Foreign Key

When you add the constraints required for identification purposes and row uniqueness, your model is something like this: If you had not shot yourself in the foot by using surrogates as the first ...
Giannis Tsimarakis's user avatar
0 votes

1 to 0 or 1 relationship when both entities are optional

I could be completely wrong but I think what you're looking for is a child linking table to represent when you have a complete 1-to-1 relationship instance. Pseudo-code: CREATE TABLE entity_1_entity_2 ...
J.D.'s user avatar
  • 37.1k
0 votes
Accepted

How should I correctly do modelling with this data?

Have a 1-many linking table PurchaseTickets ( PKey INT identity(1,1) primary Key, PurchaseID INT References Purchase(PurchaseId), TicketID INT References Ticket(TicketId) ) Remove TicketId from ...
Alocyte's user avatar
  • 422

Top 50 recent answers are included