Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

2
votes
0answers
50 views

Is this “idiosyncratic” method to manage constraints declarations beneficial?

I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this. At my company, we have a large .sql file that holds all of our schema ...
0
votes
2answers
32 views

Co working Space DB architecture [closed]

I am trying to build a platform to share the empty desks in an office with someone outside the premises to utilize the space and generate some revenue. I am thinking of three major roles: Member who ...
1
vote
1answer
43 views

Finding available schedules for multiple slots

I was asked to come up with a database schema to keep track of the slots and their schedules and query the table to find out the available time(empty schedule) for the slots. I created three tables ...
3
votes
1answer
135 views

Normalizing/combing mutliple tables with similar data

I'm working on setting up a database of vehicle records based on data from a third party vendor. There are 3 models: Vehicles, Transmissions, and Adaptors. Both Vehicles and Transmissions are 1:n ...
-1
votes
1answer
41 views

Adding Multiple Individual Indexes vs One Combined Index for Multiple Foreign Keys

Background I have a "join" table that is used to join one "transactional" table with a "category" table based on multiple columns (and through several joins): TxnTable(Entity1Name, Entity2Name, ...
1
vote
1answer
97 views

How to create a Hash Computed Column for Many Columns?

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for ...
3
votes
1answer
88 views

Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
1
vote
1answer
42 views

Suitable NoSQL DB design for a fleet of IoT devices [closed]

This is my use case: 10,000 IoT devices Each IoT device sends its environmental data (temperature, pressure, etc.) every 30 seconds Data of each device will be kept for 24 hours The main requirement ...
0
votes
0answers
33 views

data model for C 2 C business

I would like to find out what table relation would be idea for this case a Customer to Customer business transaction where one client provides a service to another for a charge. So this is purely a ...
4
votes
1answer
158 views

SQL Server Data Warehouse stored procedure location

We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse. We have a stored procedure which creates the table CustomerTransaction. Where is the best location to ...
0
votes
1answer
38 views

Recursive Relationship [closed]

I have an application, which contains two Models Question and Option Each Question has many Options Each Option may have many Questions (and this Question may have many sub Options) so on and so ...
0
votes
1answer
226 views

SQL Server Stored Procedure Slowly Changing Dimension

Can someone code review this slowly changing dimension for our Kimball Data warehouse? This is Type 2 dimension. I reviewed a lot of code on the internet, however I want to use non-TSQL specific ...
-3
votes
1answer
25 views

I need help with normalising the data derived from a survey

Background information: I have been assigned to make a survey on E-sports and build a database. My survey can be accessible here https://devpatel6.typeform.com/to/MB1DYP . In brief, this survey ...
0
votes
1answer
24 views

What is the foreign key between two children in the same hierarchy?

I'm trying to map an entity relationship design into a relational database. Under STAFF entity, I have 2 children are BUSINESS MANAGER and EMPLOYEE. My teacher requests to add a 1-M relationship ...
6
votes
1answer
1k views

Double Entry Accounting Schema

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now. Business Rules The logic, for those not ...
1
vote
2answers
72 views

Are updates the only disadvantage of denormalization?

I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table): I have read that the problems that you ...
0
votes
1answer
94 views

database and table design for billions of rows of data

basically i got 2 tables: header, details tables. CREATE TABLE `header` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RECORD_DATE` datetime DEFAULT NULL, `TICKER_ID` int(11) DEFAULT NULL, `...
1
vote
0answers
33 views

Multi tenant .NET Core application

We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant. We can give each tenant a different subdomain so that we can base the tenant lookup ...
0
votes
1answer
75 views

Design with inheritance and two 1 to many relationships to the same table

Quick schema overview: Below is a schema being a subject of my question. In the design there are two "regular" tables questions and notes as well as two "abstract" tables: elements, with "concrete" ...
2
votes
1answer
60 views

Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point?

I am designing a database for an e-commerce company. Each product will be identified by a unique ID (SKU). We want to format the SKU as LLLL000. LLLL is the parent product, for example Iphone X. 000 ...
0
votes
1answer
65 views

How do you model a recurring order in a shop?

I am making a B2B-shop system for a customer where their customers can order items from their shop. The system needs to handle placement of recurring orders like: "Every tuesday of the week send me 4 ...
1
vote
2answers
86 views

Difference between OLTP and Inmon Data Warehouse

What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB. I know what a Kimball ...
0
votes
1answer
77 views

Why should I save both markdown and the converted HTML in my database?

First of all, forgive me if this is the wrong place to ask this or if this has been answered somewhere and I just can't seem to find it. So you have a scenario where you have an article/news web app ...
1
vote
1answer
121 views

Data Warehouse Kimball Customer and Customer Address Dimensions

I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc. In Kimball data warehouse, would I utilize a bridge table to go between ...
0
votes
1answer
28 views

Address Parts - Hierarchy vs Flat

I'm using Google to geocode addresses, which breaks down an address into geographic units like country, state, city, etc. Depending on the address being geocoded, some geographic units may or may not ...
0
votes
1answer
47 views

one to many design struggle [closed]

I have two tables : CREATE TABLE fiber ( fib_id uuid primary key default gen_random_uuid(), fib_number not null, fib_object uuid not null reference cable, --useless here but imagine a cable ...
-1
votes
1answer
29 views

Determining what user data fields to protect within a database?

Besides passwords, credit/debit card info, social security info, passport info, what other types of data fields should be hashed to give it an additional layer of protection within any database? ...
-1
votes
1answer
42 views

Inserting data into a 3NF database [closed]

I have 6 tables with ~2-15 attributes each, each one of these 6 tables has an ID which is constrained from another table. This 7th table has its own ID and the 6 other table ID's, as well as a date ...
1
vote
0answers
26 views

When to use role codes and when to use supertype / subtype

What's the best practice to follow when designing a data model when it comes to choosing between using supertype/subtype and role codes? To be clear, when i say role codes I mean a table design like ...
-1
votes
1answer
16 views

Which type to pick for column that will hold a variety of data types

I'm setting up a Postgres database that will function as a storage place for data records. There's a column in the records that I'm going to be inserting that contains different kinds of data types; ...
1
vote
2answers
35 views

Optimal way to save Social media posts from multiple platforms

I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields ...
1
vote
1answer
35 views

SQL Temporal Tables Include Current State

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables. We noticed that history tables only include history. ...
0
votes
1answer
137 views

SQL Temporal Tables Begin and End Date

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For our BeginDate and EndDate, we want them to be date,...
1
vote
1answer
35 views

Use extra column to save an unaccented copy of a string instead of using unaccent module [closed]

I am heavily using the unaccent module in a table with 500.000 rows where each row contains proportionally a lot of accented characters. The issue I am facing is that my queries have become time ...
1
vote
0answers
28 views

SQLite database: many-to-many optimization (simple but large example) without IDs available?

I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
26 views

Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has ...
1
vote
2answers
41 views

New Table vs Adding Columns To Existing Table

I have a table, products with the following columns: id, name There is another table, countries with these columns: id, name, code The products have different prices across different countries (same ...
0
votes
1answer
30 views

Single or multi tenancy database recommendations

I'm really confused searching for a database(nosql or mysql) that can handle unlimited multi or single tenancy with good scaling method either by software or physical resources. So i can differentiate ...
1
vote
1answer
26 views

Looking for tree organisation for database, based on one to many relationship both ways [closed]

All the tree organisations for databases I've found documentation, papers or blogs about were built on a one parent node can have many children, but a child node can have only one parent, with a ...
2
votes
1answer
145 views

Creating a Friendships database that includes a Block Friend option

My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database. So far I ...
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 :) ...
-2
votes
1answer
72 views

About Foreign Keys that is not a primary to other tables

I'm clearly new into database implementation and I'm having doubts about my keys. I have about 5 tables namely: User, Profile, Client, Stylist and also Posts sirs. I have a certain foreign key named ...
0
votes
1answer
35 views

Advantages of a hierarchical schema for private chat

This post claims that All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. It also links another post which gives an example ...
3
votes
0answers
46 views

Third Normal Form (3NF) Decomposition Scenario

I am trying to understand what are the steps to check if a decomposition of table is in 3NF. There is a specific exercise I'm trying to solve with no success. Given that R={A1,A2,A3,A4} and F={A1→...
8
votes
1answer
638 views

Is using multiple unique constrains on a single table considered bad design?

I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique ...
1
vote
1answer
46 views

How to design a database for non-time invariant data [closed]

I am looking at designing a database where entries will probably not be time-invariant. For example, it contain entries of a customer company. The problem with a customer company is that many things ...
1
vote
1answer
52 views

Soft-deleting change-tracked user content

To begin with, I distinguish between tables containing user-generated content and tables containing information about the user. The former are associated with the user profile, whereas the latter are ...
1
vote
0answers
21 views

structuring media uploads (users, images, videos) using sequelize.js

(using pseudocode to simplify) say I have a User (users table) User { id: PK } and I want to have them upload images and videos (media). I want to be able to differentiate between images or ...
1
vote
1answer
36 views

Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys?

Consider this example (they are disjoint, by the way): There are two scenarios which I am guessing: While translating it to relational tables, the rows in PROGRAMMER are unique which do not exist ...
-2
votes
2answers
39 views

What DB should I use in my case? [closed]

I'm writing a software in C# that downloads some data from a website and stores it in a DB daily. The data is cases that were processed at that day. Each case has attributes like case_creation_date,...