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
6
votes
4answers
313 views

Relational integrity without relations

I have about 15 years software engineering experience, writing business software with relational databases. Mainly SQL Server and Oracle. I've always been of the opinion that you should define ...
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
121 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 ...
-2
votes
0answers
41 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 ...
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
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
1answer
64 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 ...
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 ...
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....
-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 ...
4
votes
4answers
388 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, ...
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
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
96 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
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 ...
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 ...
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
61 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 ...
-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
52 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
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 ...
-3
votes
1answer
113 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 ...
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 ...
12
votes
5answers
779 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. ...
0
votes
2answers
86 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 Autodesk. Licenses are sold to both ...
0
votes
1answer
172 views

Database design for a survey app [closed]

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
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
1answer
38 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
190 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
107 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
2answers
297 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
121 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
1k 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
89 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 ...