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

Filter by
Sorted by
Tagged with
1
vote
1answer
25 views

Approach to modeling for a new attribute to attach to exactly one of the many in a one to many relationship

Apologies for the awkward title; I wasn't quite sure how to concisely describe what I'm trying to do. My situation goes like this: We have a Product table in our database; Each Product may have 0 or ...
3
votes
2answers
108 views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
1
vote
1answer
63 views

Optimizing inserting/storing Large amount of data in database

We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics ...
1
vote
3answers
56 views

Having unique identifier for user while Updating user entity (by deleting and adding new one)

I am adding authentication to my web app and I consider the following case: I have USERS table which look like this simplified version Id {int} | Username {nvarchar(100)} | CollectedPoints {int} | ...
-2
votes
0answers
40 views

Can we have a binary relationship with both entities totally participating?

For example, I have an entity set Person and an entity set Personality. Each person can have more than one personality but must have a personality. Also, since a Personality P is present in the ...
2
votes
3answers
89 views

How should I best structure my app when I want to sort a “parent” model by a property computed on another “child” model?

Question about OO-programming, functional programming, NoSQL, SQL databases, and software architecture Hi all, I am a intermediate-level self-taught programmer, and have been dying to get an answer ...
1
vote
2answers
106 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
2answers
100 views

What's the best way to save/track change requests on multiple objects in a database?

I have a schema containing contracts, sub-contracts, services offered by each of those and sub-services. I also have a application that allows to extend the duration of the contract and to modify ...
8
votes
2answers
9k 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 projects and I never felt the need. I did work with professional databases that had foreign keys constraints. ...
1
vote
2answers
65 views

Sorting a list of aggregates based on associated aggregate's properties

I am modelling a users management system, where users have a country of residence. I have modeled both User and Country as aggregate roots, and I am referencing the user's country by its ID internally....
0
votes
1answer
56 views

Combining relational and document database for movies

As a architecture design brainstorm, I am pondering on how should I define schemas for movie database where heavy text searching is delegated to document based database (eg. elasticseach) while rdbms ...
4
votes
4answers
387 views

Object Oriented Design and Relational Databases: Where to place behavior that pertains to more than one tables?

I have been trying to learn Object Oriented Design, but I find it very difficult to model objects that access a relational database and do JOINs. For example, I am building a REST API, it has User, ...
-2
votes
1answer
41 views

In DBMS are Secondary Indices ordered?

I've read that the Heap File Organization is usually used to structure a secondary index. This makes me suspect that usually, a secondary index is unordered, as opposed to the primary index, that is ...
5
votes
2answers
702 views

Specifying a bi-directional unique constraint on a join table in Postgres [closed]

Problem description I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level. The following situation I consider ...
1
vote
3answers
117 views

How do I query something when I don't know how its structured?

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured? By "structure" I mean table names, fields, etc. Unless I have table names, ...
1
vote
2answers
114 views

Create a column mostly with null data SQL like

In postgresql I have a table with a list of cities. One row for city. Around 15000 rows. A column is CITY_NAME, now I have to add an english translation of the city name. The problem is that only 10 ...
1
vote
2answers
185 views

How should I handle Stored Procedure parameters that need to be the same length as a column?

I'm writing a lot of stored procedures for a SQL Server database that take an id or code that is a CHAR instead of an INT. Most of these haven't changed size in a long time, but when a customer wants ...
1
vote
2answers
48 views

Architecture of “tags” for contacts in a marketing system and hard-coded IDs so functions can refer to them

I have these tables: contacts, tags, contact_tags. (contact_tags has columns contact_id and tag_id.) Administrators can manually create new tags via an internal website. Administrators can also ...
1
vote
1answer
94 views

Database Design - Hierarchical Structure [closed]

1 We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure. We generate XML files from the database, which ...
1
vote
1answer
65 views

Designing database: split into different tables or group them together?

I am fairly new to database design and now trying to build my own forum-based application. The app will have nested categories as countries > systems > levels > subjects In every subject ...
-3
votes
2answers
110 views

How to structure a database where a record can be a child of a parent or a grand parent record

I have these concepts for a database application: Session, Subsession and File A session can have multiple files A session can have multiple subsessions A subsession can have multiple files A session ...
0
votes
2answers
89 views

Gathering data from JSON fields in a relational database

I'm working on a small e-commerce solution which uses MySQL. At first there was an initiative by the boss to use MongoDB but I suggested relational database because there are lot entities with ...
20
votes
3answers
8k views

How to store ordered information in a Relational Database

I am trying to understand how to properly store ordered information in a relational database. An example: Say I have a Playlist, consisting of Songs. Inside my Relational Database, I have a table of ...
1
vote
2answers
292 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 ...
0
votes
1answer
43 views

Event planning application database schema

I am planning to develop an Event Planning Application. Currently I'm in the stage of planning the app structure, for example database schemas. Basically there will be two kinds of users: The event ...
3
votes
1answer
139 views

One database table that is referenced by almost all the others

I have a relational database similar to the one in the diagram where there is one table called app instance which is the foreign key in almost all other tables in the database. It is meant to ...
0
votes
1answer
124 views

Database Design Structure

I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL. Requirement: My Asset Headers currently have a relation to ...
0
votes
1answer
60 views

Bridge/Join Table for a Many to Many Relationship between One Table and Multiple Others

Background I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table. Options I ...
0
votes
1answer
59 views

How to store data which can be a foreign key or free text

I am not sure how to explain my problem, but basically I have an input field which lets you enter member's name or select one from a dropdown list. If the user has selected the person from the list ...
1
vote
1answer
97 views

Persisting user-deleted items in a database

I'm building a web application, in which users have their own set of Topics. Since Topics are recorded for reporting purposes, it's important that Topics used previously are not deleted, so that they ...
5
votes
5answers
4k views

When keeping product price change history, what are the pros&cons of keeping track of price change events only?

When tracking something like pricing history, in my relational database, what is a recommended way to store pricing changes? For example I can: keep price change events only (aka a single date ...
3
votes
1answer
11k views

Best Multiple tables product with categories

I was looking to create an EAV structure for an inventory management system. But I read this answer and decided against it. I am looking to create multiple tables for all my products. But the trouble ...
8
votes
4answers
23k views

ERD: “many” vs “zero or many”/“one or many” crowfoot notation?

Background I saw this figure describing the different crowfoot notations used in ERD: I'm not able to find the difference between the "many" notation and the "zero or many. However I was able to find ...
-2
votes
1answer
72 views

Relationship between users and items tables in a database

I have two tables in a database: A users_table and a items_table. I need a way to be able to define which items each user gets access to. I thought to create a third table containing: 'user_id', '...
0
votes
0answers
33 views

Design a way for multiple and single group mapped to a Role?

Currently I am trying to setup a relationship between user groups and roles. I am trying to satisfy: Multiple roles apply to single group multiple groups can have same role multiple ...
1
vote
0answers
50 views

ACL entity and microservices database relations

I would like to build a microservices architecture to handle the following scenario: I have a building. The building has some kinds of devices. And the devices can be used by the users. In the system, ...
1
vote
2answers
125 views

Deleting each row and re-adding when saving to database

I have a structure which looks like Person ID int Name nvarchar Phone ID int PersonId int Number nvarchar Make nvarchar Model nvarchar MultiSim bit One person can ...
-1
votes
2answers
338 views

Application/Database design where two tables have both 1-to-many and 1-to-1 relationships

I have an application where the requirements are to show a list of (just for example) car manufacturers. When you click on one of the manufacturers, a list of car models show up. You can then click on ...
1
vote
1answer
252 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 ...
4
votes
3answers
2k views

Is it okay to use a non-ID column for foreign key reference?

I have my tables as below Role(Role ID, Name, Other_Columns) Command(Command ID, Name, Other_Columns) I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName) ...
1
vote
1answer
79 views

Updating sub-properties via Entity Framework

I was wondering how to updated nested objects/sub-properties when using Entity Framework. But to make things clear, let's start with an example. I have an object, which can own a number of other ...
1
vote
3answers
110 views

Is there a viable methodology for using a relational database without an Object-Relational Mapping tool

I'm beginning a small Web development project (maybe 20 tables initially). I would like to use a relational database for transactions, SQL, indexes, etc. I have limited needs for true objects, though. ...
2
votes
0answers
30 views

DataBase design generated by hibernate Many to Many for MariaDB

Hi I am in middle of designing DB and Got a little bit confused about DB Design. I am Using Hibernate as ORM Tool ahd mapping between Page and Step entity in hibernate. @ManyToMany(fetch = FetchType....
0
votes
4answers
182 views

Relational DB design: Should I skip a table with only 1 column that create relationship between 2 other tables?

I must design a database that store items that can be associated to a production. Production is only a identifier with no common attributes. But, a production may or may not have attributes (which are ...
1
vote
1answer
43 views

Storing answers of a form builder where question sections may be repeated N times

I built a form builder with which administrators of the system I work on can build forms (or blueprints as we call them). The structure of these blueprints are as follows: blueprint X section 1 ...
-2
votes
1answer
48 views

Preuso/empty entity or only real entities + nulls?

I have a table "products" and a child one "product_modifications" Some products have "product_modifications": "colour", "size",, .... But not all. When a client orders a product, views pictures and ...
0
votes
1answer
78 views

How to create a flexible datamodel?

I'm trying to create a flexible datamodel and i'm not sure what's the right approach. My application is used by several clients (big and small). Big clients need all the seperate layers, but small ...
-3
votes
1answer
112 views

Can a Database Scale Up? [closed]

Microservices are the latest software development craze. All the kids are doing it. When I started hearing about the concept, the first advantage that came to mind was the single responsibility ...
12
votes
5answers
778 views

My team is scared of relational database entities with foreign key relationships and I don't understand why

I'm relatively fresh out of college so most of my familiarity with relational databases is from my databases course where anything not in BCNF or 3NF is a travesty. Certainly that's one end of the ...
4
votes
1answer
167 views

Should I comply with SRP when designing relational tables?

As far as I know, the single responsibility principle concerns functions, classes and modules. Now I am designing a database and I wonder if this principle should be applied to relational tables. ...