Questions tagged [design-pattern]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 views

1 to 0 or 1 relationship when both entities are optional

In business process which I'm trying to model there are 2 entities which are in 1 to 1 or 0 relationship from both sides, for example: entity_1 might or might not produce one and only one entity_2 ...
bst19's user avatar
  • 1
0 votes
1 answer
25 views

How to model edited linked records of a multi-schema entity while preserving the original linked records?

If you have a base class parent. parent can have multiple child records, a one-to-many relationship. parent schema: +-----------+ | parent_id | +-----------+ child schema: +-----------+----------+ | ...
Henry's user avatar
  • 213
1 vote
3 answers
41 views

Should a datbase reflect a user's actions or user's state?

We have a Postgres DB in which we store a "settings" JSON field on Users. This JSON field indicates (among other things) whether the user is subscribed to a digest we send out. There's some ...
duhaime's user avatar
  • 129
1 vote
0 answers
19 views

Data Modeling Problem - Schema to store approval process

I need to design a schema to keep track of data changes (I do not need Change Tracking or Change Data Capture or Temporal Tables) to columns in a few tables. When the data is created, modified, or ...
DBK's user avatar
  • 378
1 vote
0 answers
18 views

How to optimize a system with balance and in-out TXs to avoid re-calculating user's balance?

I work on a service wherein a user will have balance, will be able to deposit and withdraw money, perform actions that generate and burn it. As the amount of the transactions grow, the amount of them ...
nicholas.fowler's user avatar
0 votes
1 answer
57 views

3NF Decomposition and NP-Completeness

I have a question about the computational complexity of the algorithm that allows us to decompose a schema into 3NF. My book says that the decisional problem of telling if a schema complies with the ...
lorenzo_moni's user avatar
0 votes
1 answer
75 views

What data "MUST" be stored inside a "relational database"?

I am working on a mobile/web app like Instagram. Considering that, I want to know for what part of the app it's better to use relational and for what other parts non-relational databases. I have ...
best_of_man's user avatar
4 votes
3 answers
305 views

For what type of data it's better to use relational, and for what type of data, non-relational databases?

I am trying to write my first big backend project. This is a mobile/web application like Instagram but for different purposes. As I searched through the internet I found that Instagram uses PostgreSQL ...
user20551429's user avatar
5 votes
1 answer
287 views

What's the difference between these three versions of TSQL snippet?

Version 1 DECLARE @key INTEGER = 33, @val INTEGER = 44; BEGIN TRANSACTION; INSERT dbo.t([key], val) SELECT @key, @val WHERE NOT EXISTS ( SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE) ...
Fajela Tajkiya's user avatar
3 votes
1 answer
111 views

Storing column for data that doesn't exist for majority of rows

I'm using mariadb. I have a single table that keeps track of video_count and image_count for a few different sites. I.e. type | enum('SITE1','SITE2','SITE3') ...
user2402616's user avatar
1 vote
1 answer
628 views

Adding soft delete to a database after having used hard delete

Introduction My app collects data from a centralized source where many different users can submit data about their organisation and their staff. Previously we used to just hard delete a users data ...
Mikkel's user avatar
  • 21
0 votes
1 answer
197 views

Is it ok to have multiple files table for a different entity?

I have two entities A and B. A and B can have multiple files associated to them, each of those files can be a document or an image. Does it make sense to create a_files and b_files tables with the the ...
Federico Peralta's user avatar
0 votes
1 answer
123 views

What database & design would you use for legos [closed]

I'm trying to conceptualize an interesting problem I am designing for. Using lego's (the plastic construction toys) as an analogy seems to work well. If this is a fairly recognizable problem, then I'd ...
Michael Ramos's user avatar
1 vote
0 answers
26 views

What should be correct DB schema when input data are kinda versioned?

I have been bit confused on how should I create a proper DB schema for my particular case - everithing I come up with is very suboptimal (duplicated columns and so). I think I'm missing right DB ...
jhutar's user avatar
  • 111
0 votes
1 answer
580 views

Single-entry accounting schema

I've already built a double-entry accounting schema on a previous app, and it was very complicated. For a new app I want the simplest and most elegant accounting schema possible (transaction volume is ...
Tallboy's user avatar
  • 203
0 votes
3 answers
203 views

Clean Code - On which side to place the key

I am currently modelling a database schema. In it, addresses are used several times, which follow a fixed structure. In our previous systems, the address relation was modelled in such a way that the ...
Sven M.'s user avatar
  • 123
0 votes
1 answer
28 views

how to associate columns between differnt tables [closed]

So i'm designing a database for my application which is basically a qustionaire. I designed my db as best as i could and i think it kinda works. The questionair isn't straightfoward. by this i mean ...
RooneyMUFC's user avatar
0 votes
1 answer
2k views

How to UPDATE data without locking every record the search encounters?

We have the following database specifications: version: 10.1.47-MariaDB-0ubuntu0.18.04.1 innodb_version: 5.6.49-89.0 tx_isolation: READ-COMMITTED innodb_strict_mode: ON sql_mode: NO_AUTO_CREATE_USER,...
Daan's user avatar
  • 123
0 votes
1 answer
28 views

Store fields that are subset of other fields on same table

Just wondering what the optimal design pattern would be for the following data I'm trying to model. I have a table that stores information for Orders that have been placed by customers. Orders can be ...
user2402616's user avatar
0 votes
1 answer
90 views

Mysql Database Design - identifying required patterns

I'm trying to figure out which pattern (if any) might describe the problem I'm facing when designing the database for a club membership management system. I require help in identifying the pattern (if ...
ExternalUse's user avatar
-2 votes
1 answer
368 views

Table normalisation and empty values [duplicate]

Here are two ways I can optimise mapping of a text field with several other attributes to question and answer. First approach is, creating a map table with both question_id and answer_id in which ...
Vishal Kumar Sahu's user avatar
2 votes
2 answers
364 views

When should I reference the User record or User Profile record

I know it is a common paradigm to separate tables when building a User profile. For example, having a user table, and another table called user_profile with a foreign key to the user table. My ...
chris's user avatar
  • 121
1 vote
1 answer
51 views

Database design where % of rows have unused attributes/columns

Is there a theory/practice that promotes a design by analysing the usage of attributes of a table, such that given a threshold of unused columns over a percentage of rows would suggest that the ...
Ian Vaughan's user avatar
0 votes
0 answers
35 views

One table to hold all ids related to one user - bad idea?

I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity? For example let's say i have 3 tables each containing data about one user. Should i use an ...
emma's user avatar
  • 101
0 votes
0 answers
40 views

General pattern to dedup records

I'm storing a large amount of time series data/value (varchar) with timestamp for a set of parameters (varchar) in PostgreSQL. I'm looking for a way to avoid storing duplicate if the value is the ...
faulty's user avatar
  • 153
1 vote
0 answers
177 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
James Eggers's user avatar
1 vote
1 answer
666 views

Time Series Table ( Many Columns ) Design Recommendations [duplicate]

I am storing Time Series Data in a Postgresql database. I am wondering what the performance ramifications are of the following two proposed design patterns. Option 1: A single table with a ...
Phillip Stack's user avatar
2 votes
1 answer
54 views

Database design for a simple association

I'm not so good in db/tables design, I'm mainly a frontend web developer, but sometimes I do really small apps. Always I find myself wondering if my thinkings are correct.. So I try to ask here :) ...
Luca Reghellin's user avatar
0 votes
1 answer
147 views

Storing a combination of IDs

For my scenario I have already created a database user id that is auto incremented and they have already put their company id number in the registration. In the registration table both inputs are ...
wonderhead's user avatar
2 votes
1 answer
76 views

Relational tables should contain data and not logic - correct?

Ok, I couldn't find a better title, to be honest. I'm recently landed in a new working place, and I've been asked to help with the DB. I wanted to try to explain a concept, but I'm not sure how to ...
motoDrizzt's user avatar
7 votes
1 answer
2k views

Test Driven Design for postgres procedures

I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation. I have seen that pgTap is a popular unit testing tool for postgres and ...
Russell Ormes's user avatar
1 vote
1 answer
594 views

Table Valued UDF vs Stored Proc for passing values to a SELECT statement

I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL ...
GWR's user avatar
  • 2,807
0 votes
1 answer
59 views

Dilemma of Generating tables dynamically to improve performance

Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An interviewer goes to House 1 and interviews two members Member 1 ...
insomniac's user avatar
  • 101
-1 votes
1 answer
75 views

Online Data Model Design resources [closed]

So I was reading this transcript from Brent Ozar and co. And in the question about normalization Richie talks about a book demonstrating different data model designs across verticals. Are there any ...
Uberzen1's user avatar
  • 678
3 votes
0 answers
2k views

Serialized Inventory Tracking

I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select). in order to keep stock records currently i came up with two options. Option 01 use ...
eric's user avatar
  • 143
0 votes
1 answer
113 views

How to design a database with rare multiple values [closed]

I have a "user" table, with many attributes including "nationality". the user can have multiple nationalities, so it sounds as if I have to design a new table to store this multiple value. but what ...
AliSmart's user avatar
2 votes
2 answers
4k views

How to model a relationship with variable tables (polymorphism)?

What is the best way to model the following kind relationship? Let's say I have tables representing two types of entities: company and person. I have a customer table with an entity_id field, which ...
JBentley's user avatar
  • 251
-1 votes
3 answers
109 views

MySQL optimization question : Having 6 columns or having 15 columns

Was wondering, is it better to split a table into three tables with approximately 6 columns each and using foreign keys, or using one table and have it have 15 columns ? Most of the fields are ...
Marin's user avatar
  • 131
1 vote
1 answer
205 views

Which postgres design for a nested resource that can link to the same type of resource

I have this kind of data that I need to store in postgres (ruby object). #<InitRes:0x007f8ec3734c78 @kind="RestrictionsSchema", @restrictions= [#<ParsedRes:0x007f8ec60ed218 @...
Mio's user avatar
  • 631
0 votes
4 answers
638 views

Design Pattern to Reduce Number Of Columns

I have a table with 49 columns, which is already normalized. It seems kind of excessive, but since every field is part of a form and the main entity is the form there are no relations I can refactor ...
J. Pichardo's user avatar
-1 votes
1 answer
486 views

A design pattern for multiple payment tasks

I design a system that users can has multiple payments. Let me explain about it. There is a user table. Each user has a total credit amount. Ex. user Alex has $100. He can either increase his credit, ...
Vahid Najafi's user avatar
4 votes
1 answer
343 views

Numerical data with lots of NULLs: 6NF or document database?

I have a source of numerical data mostly comprised of sensor readings, physical quantities and flags. Each associated with a datetime. | id | timetag | sensor1 | sensor2 ...
IGRSR's user avatar
  • 141
4 votes
3 answers
635 views

If the query for database too complicated, does it means the design of Database is bad?

I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad. Provide a list of customer information for customers ...
bagMan's user avatar
  • 163
3 votes
1 answer
28 views

What's the term for a schema that has field+value column rather than individual columns? [closed]

I sometimes see a schema pattern where instead of: |id|mystring|myinteger|mysecondstring| | 1|hello | 1|world! | the table looks like: |field |value | |mystring |hello |...
Tony Meyer's user avatar
3 votes
1 answer
261 views

Why would an application use one central metadata table to hold metadata for all other tables?

I've started working with an application that has an usual design: In addition to the tables that contain the actual data, there is one metadata table that contains all the information about the other ...
FrustratedWithFormsDesigner's user avatar
3 votes
3 answers
390 views

Relational tables with same content, custom solution

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this: Table foo and table foo_comments, where foo_comments contains id, foo_id and other ...
Alex's user avatar
  • 131
0 votes
1 answer
390 views

Encapsulate table with view

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive ...
Václav Starý's user avatar
11 votes
3 answers
13k views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People -----...
Ragu's user avatar
  • 211
0 votes
1 answer
1k views

How would you design a table for approval system between User and Sports Club

I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club ...
python's user avatar
  • 101
6 votes
2 answers
4k views

Relational Design - One table, two foreign keys or Two tables, one foreign key each

Looking for some advice related to optimal design in the following scenario. There is a Cases table (represents cases of inventory) There is a LocationInventory table (represents locations with ...
crichavin's user avatar
  • 403