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]

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
19 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 ...
2
votes
2answers
56 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
31 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
1answer
58 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
44 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
392 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 ...
-4
votes
0answers
33 views

Live Web chat application [closed]

I need to create a live Web chat application for a website and I have been researching on that for a while. The plan is to use vue.js for client side, MySQL for data base and Web sockets. There are ...
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
421 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 ...
87
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
35 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
40 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
135 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
37 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
44 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
154 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
156 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
213 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
38 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
170 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
42 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
114 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
394 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
127 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
288 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
174 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
172 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 ...