Questions tagged [design-pattern]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
20 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 ...
0
votes
3answers
60 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 ...
0
votes
1answer
21 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 ...
0
votes
1answer
41 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,...
0
votes
1answer
18 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 ...
0
votes
0answers
33 views

OLAP cube designing: runtime generated measures

I'm facing a requirement that I'm not entirely sure that can be achieved by pure OLAP designing (anyway, my OLAP knowledge is SO small). I have a dimension table such as DROP TABLE IF EXISTS ...
1
vote
1answer
27 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 ...
0
votes
0answers
81 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 ...
0
votes
0answers
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 ...
-2
votes
1answer
43 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 ...
2
votes
1answer
18 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 ...
1
vote
1answer
40 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 ...
0
votes
0answers
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 ...
0
votes
0answers
23 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 ...
1
vote
0answers
59 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 ...
1
vote
1answer
162 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 ...
2
votes
1answer
43 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 :) ...
0
votes
1answer
48 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 ...
2
votes
1answer
63 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 ...
6
votes
1answer
864 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 ...
1
vote
1answer
319 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 ...
0
votes
1answer
47 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 ...
-1
votes
1answer
49 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 ...
3
votes
0answers
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 ...
0
votes
1answer
67 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 ...
0
votes
2answers
2k 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 ...
-1
votes
3answers
69 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 ...
1
vote
1answer
77 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 @...
0
votes
4answers
148 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 ...
-1
votes
1answer
357 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, ...
4
votes
1answer
210 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 ...
4
votes
3answers
384 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 ...
3
votes
1answer
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 |...
3
votes
1answer
107 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 ...
1
vote
3answers
194 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 ...
0
votes
1answer
307 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 ...
10
votes
3answers
8k 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 -----...
0
votes
1answer
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 ...
4
votes
2answers
3k 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 ...
3
votes
1answer
278 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 ...
1
vote
0answers
233 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 ...
-1
votes
1answer
43 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 ...
17
votes
1answer
5k 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 ...
1
vote
1answer
848 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 ...
1
vote
1answer
586 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 ...
0
votes
2answers
121 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, ...
2
votes
2answers
1k views

Normalising shared entities and applying constraints

I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many. For example, one may have the generic entity "...
0
votes
1answer
72 views

database design for optional association and temporal

I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities. +-------+ | Task | | | +-+--+-...
0
votes
0answers
95 views

Tables with same schema in DB for different sources. Bad pattern?

The main purpose of the system being developed is to collect data from different sources, process and standardize it, and then add it to the DB. After information from all sources is collected it ...
3
votes
2answers
1k views

What and How is the ID antipattern?

What is the ID antipattern and why / under which circumstances is it considered an antipattern? The id antipattern is having a unique ID column in each table. The ID is generated for each new ...