Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [design-pattern]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
25 views

Design database in which a table structure depends on value of a column

I am designing a database which will hold results of some atomistic calculations and I face a design problem. Namely, in a table named physical_properties I have an ENUM field type_of_structure which ...
user avatar
0 votes
1 answer
59 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 ...
user avatar
  • 1
0 votes
1 answer
38 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 ...
user avatar
0 votes
1 answer
52 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 ...
user avatar
  • 103
1 vote
0 answers
23 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 ...
user avatar
  • 111
0 votes
1 answer
158 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 ...
user avatar
  • 203
0 votes
3 answers
104 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 ...
user avatar
  • 123
0 votes
1 answer
23 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 ...
user avatar
0 votes
1 answer
399 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,...
user avatar
  • 123
0 votes
1 answer
20 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 ...
user avatar
1 vote
1 answer
54 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 ...
user avatar
0 votes
0 answers
250 views

modelling bill split app schema

I've been trying to model an efficient schema for a bill split app, the requirements are as follows: There can be groups in which there can be multiple participants, and conversations consisting of ...
user avatar
0 votes
0 answers
33 views

Adding a new table to a database - are my dependencies off?

So I'm adding some functionality to an existing table for a system I'm working on. It has a few foreign key relationships and it follows the snowflake design, please see below. I've censored out the ...
user avatar
-2 votes
1 answer
172 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 ...
user avatar
2 votes
1 answer
49 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 ...
user avatar
  • 121
1 vote
1 answer
43 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 ...
user avatar
0 votes
0 answers
30 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 ...
user avatar
  • 101
0 votes
0 answers
30 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 ...
user avatar
  • 153
1 vote
0 answers
71 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 ...
user avatar
1 vote
1 answer
371 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 ...
user avatar
2 votes
1 answer
45 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 :) ...
user avatar
0 votes
1 answer
52 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 ...
user avatar
2 votes
1 answer
70 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 ...
user avatar
7 votes
1 answer
1k 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 ...
user avatar
1 vote
1 answer
411 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 ...
user avatar
  • 2,487
0 votes
1 answer
49 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 ...
user avatar
  • 101
-1 votes
1 answer
55 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 ...
user avatar
  • 678
3 votes
0 answers
1k 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 ...
user avatar
  • 43
0 votes
1 answer
69 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 ...
user avatar
0 votes
2 answers
3k 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 ...
user avatar
  • 221
-1 votes
3 answers
89 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 ...
user avatar
  • 113
1 vote
1 answer
107 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 @...
user avatar
  • 567
0 votes
4 answers
363 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 ...
user avatar
-1 votes
1 answer
416 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, ...
user avatar
4 votes
1 answer
252 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 ...
user avatar
4 votes
3 answers
553 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 ...
user avatar
  • 63
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 |...
user avatar
3 votes
1 answer
115 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 ...
user avatar
3 votes
3 answers
265 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 ...
user avatar
  • 131
0 votes
1 answer
346 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 ...
user avatar
11 votes
3 answers
11k 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 -----...
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 ...
user avatar
  • 101
5 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 ...
user avatar
  • 393
3 votes
1 answer
407 views

Multiple Databases for one web solution

I am working on a new project and I am in the process of designing the Microsoft SQL Server database behind the system. The system will contain an infinite amount of customers and each customer has ...
user avatar
1 vote
0 answers
240 views

Design pattern for storing a database of databases

I have been tasked with designing a cloud platform that allows a user to manage the menus of a chain of restaurants from one place. The menus are basically massive TVs at the ordering counter. These ...
user avatar
  • 11
-1 votes
1 answer
44 views

INFORMATION_SCHEMA Pattern

The INFORMATION_SCHEMA views are a standard set of views that most major DBMS provide. What is the name for this design pattern? For instance, let's say there are a dozen source databases for ...
user avatar
  • 327
23 votes
1 answer
6k views

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will ...
user avatar
  • 331
1 vote
1 answer
940 views

How to manage many-to-many relationships when there is also a hierarchy among the many?

I have these entities – employee, task, phase, project – and these relationships: project 1:m phase phase 1:m task employee m:m task, phase, project The first two relationships form a ...
user avatar
  • 311
1 vote
1 answer
613 views

Make 3 separate tables, or 1 combined table for similar data?

When designing a database's structure, if we have multiple tables with similar data, should these tables be combined, with an added ENUM field to denote the table type; or should 3 separate tables be ...
user avatar
0 votes
2 answers
123 views

Inheritance of unknown type at creation time

Say I have a table called People into which people are inserted from the moment they are born. Now lets say I want some subclasses of people based on their job. So there are also tables for Plumber, ...
user avatar
  • 101