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.
0
votes
0answers
29 views
Subscription System Architecture
Currently, I am having tables like coupon, plan, subscription, invoice
How to make the coupon table such that to give a deal like 9$ for a month and 20% off on all yearly plans. Can anyone give me ...
3
votes
2answers
64 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
32 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 ...
1
vote
2answers
51 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 ...
1
vote
0answers
13 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 ...
2
votes
2answers
75 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. Microservice A stores the User entity in its own DB if User is configured by an agent.There is no flag ...
1
vote
1answer
63 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
47 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
403 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
59 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
29 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
4k 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
427 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
36 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
138 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
39 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
35 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
45 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
162 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
145 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
157 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
42 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
33 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
150 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
214 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
36 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
39 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.
...
3
votes
2answers
173 views
Is using -1 as a special Id a good idea in SQL?
I'm trying to determine if using '-1' in an Identity column has any drawbacks, or if there is a better way to do what I want.
In my database I have User table and a Group table. All users must belong ...
2
votes
1answer
43 views
Best practices for app-cloud synchronization of database representation
I'm planning a cookbook-like Android app that would let users store/add/edit their e-juice recipes, keep track of the ingredients, etc. Internally, I'll be storing the data in a database on the user's ...
2
votes
2answers
118 views
How to store sequential timespans in a database?
How can consecutive periods of time (defined by a start and end date) be stored in a database?
For example:
from 2018-01-01 00:00:00+01:00 until 2018-06-01 00:00:00+02:00
from 2018-06-01 00:00:00+02:...
2
votes
2answers
114 views
How to design ID code system for people database?
We are restructuring our entire database / filesystem / user identity system. As a first step, we have determined that we need to assign each user/employee in an organization unique ID. Surprisingly ...
0
votes
0answers
35 views
Time series database for low-resolution data
Does it make sense using a time series database, such as opentsdb, for very low-resolution data (hourly, daily) or I should rather use a relational/NoSQL database engine instead?
It's quite possible ...
1
vote
3answers
68 views
Database schema for point-in-time data
What’s a good strategy for storing point-in-time data in a relational database?
For example, let’s say you have products and orders. Every order has some products. But they’re not the products as ...
1
vote
0answers
36 views
separate table for one of the type or java enum?
I want to make budget tracking web-app, tracking all savings and expenses with corresponding dates. Later generating reports, summaries etc..
I came up this design
Frequency Table - does it have to ...
3
votes
3answers
407 views
One Database for Multiple Applications?
Has anyone worked in an environment where multiple in-house applications are all under one database with each application given its own schema within that single database? I don't just mean a few ...
3
votes
4answers
129 views
DB Enumerations vs App Enumerations vs Lookup tables
We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices.
Have the enumerations as ...
1
vote
2answers
295 views
How to process huge amount of data with limited processing ressources? [closed]
I receive daily from an external source a very large amount of data (around 250GB with 260 million rows of fixed width text) distributed over 5 text files. I am writing a Java application that should ...
-1
votes
1answer
65 views
What is the best Architecture / System Design to launch same app in different flavor and a bit different logic?
There is a company let's say "X" which mainly deals in Real Estate / properties and has a website (online portal) to display those properties.
Just like any other big company when they saw a ...
-3
votes
1answer
85 views
Database design for online applications
I am developing an Online Application portal. But I am stuck in designing my database tables, and am unsure about the best approach to take.
Background: how the portal is supposed to work
Let me ...
2
votes
3answers
151 views
How should I implement custom database fields?
I'm building an app where end users can add custom fields to database tables. Think of it like a CRM app, where sometime you just need to add an extra field to your customer table. Pretty much all ...
1
vote
3answers
57 views
Is this implementation for the “one” side of the relationship correct?
Say I have the following ER diagram:
The "one" side of the relationship of the above diagram means the following:
Each student can meet 0 or 1 teacher.
So I can't do something like this:
But can I ...
1
vote
3answers
175 views
Is there any risk to having row IDs visible to users?
In this article, blrfl makes a good case (How to design a good receipt number) for keeping things simple. Don't encode information into numbers - any information needed can be looked up. I am very ...
0
votes
3answers
173 views
Does the “one” side of the relationship also apply to the same record?
Say I have the following ER diagram:
What I know is that the "one" side of the relationship means the following:
Each student can meet 0 or 1 teacher.
So for example student3 can meet teacher7, but ...
1
vote
1answer
49 views
Better to store miscellaneous metadata in database or calculate on each access
I have a number of attributes I need for various page loads and other backend tasks, and I'm debating on whether storing these things in a database or calculating them on the fly.
For instance, if ...
0
votes
0answers
20 views
mongodb/mongoose - ensuring uniqueness between two lists
So I currently have my list and item collection set up (mongoose).
List
new Schema(
{
items: {
index: true,
ref: "items",
type: [ObjectId]
},
...
