Stack Exchange Network

Stack Exchange network consists of 175 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 [relational-database]

A relational database is a digital database based on the relational model of data. This model organizes data into one or more tables (or "relations") of columns and rows

0
votes
2answers
57 views

What is the best method to avoid company data duplication in mixed B2C and B2B product in account creation?

I'm building a user license portal, store, SSO, and mini CRM and admin for internal people to manage those users for a niche 3D software company, similar to Autocad. Licenses are sold to both ...
0
votes
1answer
31 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 ...
1
vote
2answers
62 views

Reporting with cyclic or relationships

I’ve got me a design and implementation problem extracting and formulating the results I need. What I’m trying to do is build some reporting on forecasted stock commitments in an inventory system to ...
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 ...
2
votes
2answers
108 views

How to structure database for daily events?

I'm storing data which logs whether or not a user has logged their attendance for a given day. Some days are unimportant (holiday, weekend), so those are also stored. The two requirements are that: ...
1
vote
3answers
90 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
vote
1answer
41 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
3answers
438 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 ...
88
votes
9answers
16k 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 ...
0
votes
2answers
173 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
2answers
39 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
56 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
3answers
157 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
44 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 ...
4
votes
1answer
143 views

What tactics to use to load the referenced data in repository implementation?

Say that I have a repository that is used as READ-ONLY data provides (never for the writes/updates) - the query part of the CQS, NO event sourcing, hitting relational DB. One of the essential rules - ...
-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, ...
2
votes
2answers
273 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 ...
3
votes
2answers
175 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
329 views

How can I turn my CRUD database application into a REST application?

I may have gotten myself confused, when reading CRUD vs REST and how they are similar, so I wanted to explicitly ask: can I turn my CRUD application into a REST application, when I have a specific ...
3
votes
4answers
181 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
3answers
61 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 ...
0
votes
3answers
177 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 ...
2
votes
1answer
188 views

Web application with relational database - best practice?

I am currently designing a web application which is pretty similar to Twitter but with some unique features for my company. Technologies are Vue2, Spark framework and PostgreSQL. I am trying to ...
6
votes
3answers
212 views

How to solve form submission consistency in a web application?

Imagine that you have a form with which you can edit some data, let's call it a Person form. Let's imagine that a user opens up this Person form, receives the current data that is stored in the ...
-2
votes
1answer
104 views

Relational Database Design with a table having redundant information

I am creating a database table that will store a single number 1 thru 16 in 2 fields (there will be many rows of data). I'm trying to avoid duplicating a lot of data and make my dbase most ...
1
vote
1answer
65 views

Which datastructure to use for temporary storage of intermittent and asynchronous sensor data?

Here an outline of the setup: Input data: I have several sources for sensor input data (GPS, and a bunch of other sensor data from different sources). All of those individual inputs consist of a ...
4
votes
2answers
157 views

Is it okay to structure a non-normal table in relational database to achieve a fast query?

Let's say we've got 3 entities here, User, Tag, Track and a single associative entity called Tagged. I need to count a tag frequency of a track and the common way is to count the record of tagged, ...
1
vote
0answers
115 views

How to maintain table relationship across services in with a micro-service architecture

I have a scraper service that looks for media online. For every source type, there are two tables. A common source table and a source_type_media table that are related by source.id to ...
0
votes
1answer
33 views

Extending photo album app with videos

I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension. The current design ...
0
votes
5answers
271 views

How to create a db schema for “if this then… elseif … elseif …” and so on?

I want to create an app where a user will go through a series of steps every day. All the subsequent step will depend on the previous ones, that is, 2 users can have different experiences depending on ...
2
votes
2answers
2k views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my project and I never felt the need. I did work with professional databases which had foreign keys constraints. ...
2
votes
3answers
233 views

Database design for generic behaviour

I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something. Initially, I had thought that to make it possible to ...
1
vote
1answer
74 views

What are good ways to encode order between records of a relational table

For example, when an issue tracker offers the functionality to users to keep a prioritized backlog of tasks and used a relational database for persisting this information, what are effective ways to ...
1
vote
0answers
50 views

Multiple version of database content

I am working on schedule module of a project management software, every project has two types of schedules. Base Schedule Actual Schedule The actual schedule is a copy of base schedule which is ...
0
votes
0answers
389 views

Use of UUIDs (Primary key) in a REST API

I faced the problem bellow when I designed our new app. We are thinking to have one extra column for some tables which is a UUID for security purposes. So, there are two different solutions. First ...
2
votes
3answers
174 views

Deal with Enumerations (Lookup tables)

I am trying to design the DB schema of my new project and I need some suggestions. I decided to create a lookup table in order to save the enumeration values. So I have a table Status with these ...
5
votes
5answers
1k views

Use UUID on top of auto increment ID

I am trying to design the DB schema of my new project and I need some suggestions. I am thinking to have one extra column for some tables which is a UUID. I wanted mainly for tables which contain data ...
1
vote
2answers
87 views

How to model a status log that doesn't allow duplicate statuses sequentially

I need to model the status of an object over time. I'm looking for the best way to model this so that concurrent database connections can look at the current status and update the status if the newer ...
0
votes
1answer
92 views

How to make an server side application independent of database

We have a server-side application (in JAVA, communicates via web services) that accesses a database. If we wish to sell just the application to a customer, so that he can use his own choice of ...
1
vote
2answers
96 views

Using Autoincrement Primary keys as Foreign Keys vs. unique codes

i’d like to push discussion on the same named thread again. I also use auto increment primary keys as foreign keys - mostly for code tables. Every row also has a code for identifying entries for ...
0
votes
3answers
168 views

How to associate one type with another in multiple contexts?

An application has types Game (and other business units) and Message. (Message is not a simple class with text, but a composition of a variable number of blocks like texts, images, and videos). ...
3
votes
2answers
159 views

Storing calculation results in DB

What I was taught in the university was we don't save the results in the database, but only the raw data which need for the calculation. For a very basic example, if: a + b + c = z we only save a, ...
1
vote
1answer
57 views

Imposing conditional entity relations

While working on an application, I am facing an issue. And I am not sure how to solve this. For explanation I have managed to come up with a simple example below: Consider a Grocery Store ...
0
votes
0answers
49 views

Strategies to migrate a SAP ADS database to postgres

I am looking for a good strategy to migrate an old and big SAP ADS (formerly sybase) database system to a modern RDBMS (most likely postgres). The first and foremost problem I am facing is, that the ...
7
votes
3answers
390 views

System Design: Very Large CSV Imports Every Month

We have a webapp that will rely on large CSVs from external vendors every month. When I say large, we are looking at around 6gb so a few million rows. Probably, 2-5 CSVs. This webapp will also allow ...
-2
votes
2answers
130 views

Are there benefits to having a SQL-like read only database layer over a Blockchain distributed ledger? [closed]

Distributed Blockchain datastores provide inmutability to recorded transactions and only allow to add records accepted by a consensus algorithm in sequence. You could store data on the blockchain or ...
3
votes
3answers
744 views

Is it OK to have a many-to-many relationship where one of the tables involved is already a junction table?

When planning our database, we ended up with a setup like this: We have Company, Product and Person. There is a many-to-many relationship between Company and Product, through a junction table ...