Questions tagged [design-pattern]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
29 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
18 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 ...
0
votes
0answers
11 views

Building a data cube in a data warehouse through Inmon architecture?

I have been reading about data cubes and Kimball. Through building a star schema, one can then model a cube using its measures and dimensions of the star schema. But then there is a different data ...
0
votes
0answers
42 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
58 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 ...
0
votes
0answers
32 views

Create new table with unique foreign key or Nullable columns for one to zero/one relation? Better design?

I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up. I am using Postgres 10. Basically, I have a User table ...
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
45 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
60 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 ...
4
votes
1answer
432 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
131 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
44 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
40 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 ...
2
votes
0answers
727 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
61 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
1k 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
54 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
65 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
102 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
270 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
167 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
256 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
87 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
136 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
276 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
5k 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
802 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 ...
2
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
128 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
210 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 ...
13
votes
1answer
3k 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
728 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
539 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, ...
1
vote
2answers
920 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
71 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
70 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
352 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
361 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
829 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
215 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 ...
3
votes
2answers
284 views

Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following: Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-...
2
votes
1answer
1k views

Attribute and Value tables on SQL - Is this a good practice?

I'm not a SQL expert and I'd like to ask for the best practices on the following schema. On my current project, we're using Attribute and AttributeValue tables quite often. I'm thinking this might ...
3
votes
2answers
196 views

What is the correct way to specify a record as the *current* record?

The two options I have thought of is either to add a column boolean column called Current onto the table; but I'm sure that would violate some level of normalisation due to more than one record being ...
5
votes
4answers
7k views

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for ...