Questions tagged [database-design]
For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
5
votes
4answers
213 views
Do I really need triggers for relational database, for example, PostgreSQL?
I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database?
For ...
3
votes
2answers
47 views
How to design a multi-year statistics database?
I'm designing a database for name statistics (how many people where given that name). The data consists of names, and numbers for how many men and women were given that name within a time period (...
0
votes
1answer
28 views
Database design for a survey app
I'm creating a survey app and I don't know how to design database. I need surveys with multiple questions and multiple types of questions. There are 3 ways that I could think of:
a) Create a ...
0
votes
0answers
25 views
Alternatives to RDBMS For Forecast Data
All,
I currently capture well structured forecast data in a SQL Server. An example of this data is below:
What this tells me is that each day, I receive a forecast for the next three days. Note ...
-2
votes
1answer
44 views
Designing a facial recognition systems database
First of all, I hope im in the right stack exchange.
So I'm trying to build a facial recognition system, e.g. one that recognizes a face and compares it to a database of known faces. For the first ...
1
vote
0answers
41 views
Strategies for dealing with Stale data and Fetching
I'm working with firestore and have encountered several situations where a document will contain stale data which is a duplicate of the source of truth located elsewhere in the database.
For ...
0
votes
1answer
33 views
In a parent table - Child table relation , where to store order of children
Assume a Parent -> Child relationship in RDBMS.
questionnaire table and question table.
question table has a foreign key questionnaire_id to store which questionnaire it belongs to.
Questionaire ...
0
votes
0answers
63 views
What should I do if I am unfamiliar with my organisation's codebase? [duplicate]
I've recently started my first job as a software developer at a small startup company.
I do not have a degree in a software engineering related field, although, I have very recently completed an A-...
1
vote
0answers
21 views
Test Results Database Design
I am trying to write a database to store test results for components, against a serial number, with different components potentially having different tests. I want to prioritise ease of adding new ...
1
vote
1answer
42 views
How/when to normalize during ETL?
Let's say you're loading a denormalized flat file of purchase transactions that looks like this:
| location_name | location_zip | product | product_price |
|---------------|--------------|---------|--...
3
votes
6answers
195 views
Should I use entity framework for CRUD and let the database handle the complexity that comes with high end queries?
I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed.
Today I got into the argument with my colleague who has ...
0
votes
2answers
93 views
Should I make a new table when a column only is populated when another column has a certain value
Lets say we have some configurations stored in a database for how we should acquire some data. One of these fields is an AcquisitionStrategy which denotes how we will acquire the data.
I want to add ...
1
vote
1answer
39 views
Regarding data modelling in a NoSQL use case
I am reading the following link to learn about system designs of various systems. https://www.educative.io/collection/page/5668639101419520/5649050225344512/5673385510043648
(This is a paid link, I am ...
-3
votes
2answers
236 views
Database Schema for a multiplayer quiz game
I'm trying to accomplish this scenario :
There are 2 types of users, let's say Admin and Worker, and there have different roles.
Admin can do a CRUD of questions, and also can create a room where ...
2
votes
4answers
127 views
Database management good practice - should it be in code or with database tools?
What is good practice of settings up a database in potentially large project - creating tables and updating them - should it be done in code of the app or should it be done by external database ...
1
vote
1answer
45 views
What is a good way to add extra info to data-entries from a website front-end?
I want users to be able to dynamically add 'columns' from the front-end of the website. I understand that it is probably not best practice to actually add columns to a table from the front-end, so I ...
1
vote
3answers
87 views
Database relation design
When I develop apps I reach situations like this frequently but I never found a best practice to solve it.
Imagine:
We have chats, each chat can have many message.
We have tickets, each ticket can ...
-1
votes
1answer
36 views
TEXT column as key for a table when there are other tables referencing that table?
I have a UNIQUE TEXT column (“name”) in SQLite for one of my tables (let’s call it “table A”) and i’m considering using that column as the key for the table
The reason i can see for continue using a ...
0
votes
1answer
93 views
My MySQL database architecture for ecommerce website
I'm making an ecommerce site, using MySQL for storing data. Here is my ERD:
CREATE TABLE `admins` (
`id` int(10) UNSIGNED NOT NULL,
`shop_id` int(10) UNSIGNED DEFAULT NULL,
`name` varchar(75) ...
3
votes
2answers
70 views
Approach for storing lots of data of unknown type in database
I have an web app to which external applications can be connected to send their data. This is for telemetry purposes.
The web app is not controlling what kind of telemetry data is sent to it - it can ...
1
vote
1answer
40 views
Using UNIQUE (col1, col2) or using a composite primary key for a relation table in SQLite/SQL?
i’m working on a project where i’m using SQLite and where i have a few relation tables looking similar to this one:
CREATE TABLE tag_entry_relation
(
id INTEGER PRIMARY KEY,
tag_id INTEGER REFERENCES ...
2
votes
2answers
61 views
Index / Unique on `deleted_at` column?
We have an Accounts table in a Postgres DB that has a unique index on user_id and product. Users can have many accounts, there's no product table. Occasionally users will create an account ...
2
votes
0answers
23 views
Designing provisioning strategies
I'm trying to redesign a provisioning application that administrates other application through their REST API.
The application is quite simple, we have groups, we have users and roles and we have a ...
3
votes
3answers
169 views
Aggregation of data from two Microservices
I have two Microservices A and B.
B Microservice has a large set of an entity called User.
A Microservice stores the User entity in its own DB if User is configured by an agent. There is no flag ...
1
vote
1answer
66 views
A table for each field type or one all-in-one table model?
I am creating an application where users can create forms with many fields of text, numeric, date and select types.
The model will have a forms table referenced by responses table;
It also have the ...
1
vote
1answer
70 views
Designing SQL table for prices changing in time
I'm developing a database with a price list table for my workplace. I came with two major options, and would like to have your opinion :)
It's rather simple, containing: Id, description and price
...
2
votes
3answers
434 views
Database : Does it make sense to choose the zipcode as a primary key for an address?
I was following a tutorial on the normal forms of SQL databases, and I got confused landing on this example : https://www.tutorialspoint.com/sql/third-normal-form.htm.
From
CREATE TABLE CUSTOMERS(
...
2
votes
1answer
64 views
Proper way to associate data to users that are stored in external database?
I have this IdentityServer which handles authentication and authorization. It saves users, roles, claims, etc in its own database.
Then, I have a web app that needs to protect some resources to ...
1
vote
1answer
32 views
Multilingual reference/lookup tables and reference tables with enums
I will use the reference tables for the first time so I have some questions about it.
Example Table
------------------------
id | description |
------------------------
1 | Some description1 |...
26
votes
4answers
5k views
Is there an industry standard for gender model other than male and female?
I am modeling a database that should be used as generic non-functional requisite for all services of the startup company, like persons, users, services and commercial data like coupons, signature ...
6
votes
2answers
444 views
Scalability: Millions of tables vs one big master table and millions of views
We have to migrate an old (million LOC) system from SAP ADS (formerly Sybase) ISAM based (so called free tables) based system to a PostgreSQL database.
The system implements kind of multitenancy ...
88
votes
9answers
15k views
Is it ever okay to use lists in a relational database?
I've been trying to design a database to go with a project concept and ran into what seems like a hotly debated issue. I've read a few articles and some Stack Overflow answers that state it's ...
1
vote
1answer
41 views
Client (app-server) vs DB generated unique ID's
I've been reading on instagram's approach to generate unique identifiers.
Besides remaining content I'd like to understand on of the points their making as a con of client-generated (by client I mean ...
1
vote
1answer
41 views
Bulk Inserting Data with Duplicates or Line by Line Insertion Without
I am loading a large amount of data (2,000 files with about 30,000 rows, 30 columns) for use in a Django application.
The files are based on 40 different templates. The column names are consistent ...
0
votes
2answers
165 views
Is there any benefit in having more than one table for storing the same type of objects?
Lets say I want to store songs in my database. Instead of having just one Song table, I have ten tables. The Song table also has a foreign key to the Artist table. When an artist is added to the ...
2
votes
1answer
41 views
User management: third party Identity Management vs local user
Context
Let's considering a standard web application handling cars. Each car has an owner. So the car structure looks like:
cars(
id INTEGER PRIMARY KEY,
...
owner_id *???*
)
For simplicity ...
-2
votes
2answers
37 views
database/orm model design for contacts
I am building a internal crm which has contacts which are just people with first name, last name, email, phone etc. Contacts can be one or more types e.t.c. job candidates, hiring managers, customers, ...
1
vote
1answer
53 views
Best way to model pricelist per customer for products in webshop
I am making a b2b-webshop system that will have multiple customers. Each customer will have a separate active pricelist which contains the products that they can buy and the prices that applies for ...
1
vote
4answers
340 views
How to handle common data in multi-tenant applications
I am designing a multi-tenant application which will use the same database schema for a separate DB instance per-tenant (i.e. every time I get a new customer, I create a new database for them). This ...
1
vote
3answers
153 views
Who guarantees the data integrity of a denormalized table, me or the DBMS?
In the following database, School_has_Student is a denormalized table :
Now if I decided to change the "paul" student name to "john" in the Student table, then the "paul" student name that exist ...
1
vote
4answers
167 views
When denormalizing a database, do I need to keep the parent tables?
Say I have the following ER diagram:
To implement the above ER diagram as a normalized database, I would create a School table, and a Student table, and a School_has_Student table (that only contains ...
-1
votes
1answer
43 views
Databse model: Entity that can have values from two different tables or user input values
I'm modeling a database, where I have a table called "appointments" (citas in spanish, but I would translate the tables's names for better understanding) where the user can create an appointment to ...
-1
votes
3answers
102 views
DB architecture for managing items of different categories
I'm making a rental app which has many categories with unique properties
Example:
Category 1
Car which has
Fuel, model, color, speed
Category 2
Apartment which has
Rooms, lift, ...
0
votes
1answer
40 views
Watchlist polling API NoSql vs Sql Frequent Updates
I am making a watchlist type application that will get all items being watched and then poll an api every ten minutes or so to check on its status.
I am trying to design the database portion of this ...
3
votes
4answers
154 views
How are account balances persisted
I am currently building a finance application that contains "accounts"
Each account has a balance that is dynamically generated based on platform charges vs payments. e.g simplified example.
$...
2
votes
2answers
259 views
How to design a relational database where users can create custom fields
Ive been looking around for a way to design a database for large amounts of leads (users) with custom fields.
Ive check this out (How would you design a user database with custom fields), but this ...
0
votes
1answer
43 views
How to resolve multiple user serve on multiple location appointment booking system
I'm trying to build an appointment system but at one point I stuck
Problem:
I define available slots for the next 7 days with help of a cron.
I have multiple users serve service more than one ...
0
votes
1answer
44 views
Descriptive short term for a field that has been set to read-only in anticipation of removal?
I'm currently writing a design to add the possibility to mark a field or attribute for an entity as read-only in preparation for imminent removal. The idea is that, once the field has been thus marked ...
-1
votes
1answer
50 views
Calculating time an object stays in a state
I use Python sqlalchemy to store my model in a MySql database. One of my objects has a 'state' field (for simplicity let's assume there are two states: AVAILABLE and UNAVAILABLE).
There are ...
0
votes
1answer
45 views
Representing classes hierarchy in database (JPA Inheritance)
I'm working on staff management application for a clinic, basic scenario, but learning about JPA inheritance lead me to over think some parts, and I need help to clear my thoughts and design.
...
