Questions tagged [design-pattern]

The tag has no usage guidance.

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

Is it a fundamentally flawed design concept to put everything in the same database (with different schemas) and only ever connect to that database?

I've long wrestled with an annoying problem: If I put different "projects" in different databases, it immediately becomes a huge pain to connect to both, or to exchange information between ...
0
votes
0answers
37 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
37 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
16 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
39 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
22 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
54 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
101 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
38 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
46 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 ...
5
votes
1answer
757 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
255 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
41 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
936 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 ...
-1
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
62 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
74 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
130 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
334 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
197 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
330 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
27 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 |...
2
votes
1answer
88 views

Unusual database design - any benefits?

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 ...
1
vote
3answers
182 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
302 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
7k 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
968 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 ...
3
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
220 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
230 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 ...
16
votes
1answer
4k 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
809 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
569 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 "...
1
vote
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
89 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 ...
2
votes
0answers
378 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to 1....
4
votes
1answer
1k views

Optimum design / structure for product data database?

What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values ...
1
vote
0answers
44 views

Advice on designing databases for things like Posts and categories; and general rule of thumbs [duplicate]

I'm creating my own website with a capability to CRUD posts and other related stuff like category, tags and other things (I believe it's similar to the first two mentioned). Posts: (current create ...
5
votes
2answers
372 views

Is there a well-known name for this “poor man's ref. integrity” schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin ...
4
votes
2answers
941 views

Data Modeling for different roles

This is my first post here, so I hope its clear enough. I want to make an application for people who sell, buy, rent and fix a certain product. I need to create a database that would keep the data of ...
1
vote
0answers
222 views

Best practices for ranking records in flexible way

Suppose we have 10000 records of any product. User search on our website with his given criteria. so we output search results based on given criteria. Now Admin want to rank products in search. so ...