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
0
votes
1answer
65 views

Selective table CRUD depending on value

I am trying to have a lean database size, so I thought that if an input, which will always be a string, should be checked for its value type and therefore saved in a specific table that is appropriate ...
1
vote
1answer
65 views

How to implement arranging objects into hierarchical structure in a relational database

I'm developing an application where users can create certain business objects (for simplicity let's just call them 'objects') that are stored in table in a relational database (the column names are ...
1
vote
1answer
87 views

How to model shared and unique options for a family of products for a relational database?

I have a family of products that currently houses 3 product lines. All 3 product lines share the same 4 options, but on top of those, each product line has 1, 2, or 3 different options respectively, ...
2
votes
1answer
424 views

Are Timeseries Databases useful for non-numerical data?

I have a huge time series (about 30 million) of network paths with the following format: timestamp, path, latency The path is a sequence of IP address, so it can be represented either as a string or ...
1
vote
1answer
143 views

Database Relationship from N t… 1 to N .. N

Team can have N users and users can have N teams. One user can have N answers. Answer belongs to one user. I need help to build database structure. Important is to get team record from any answer. ...
8
votes
3answers
2k views

Is ORM a bad tool for tree-like DB structures?

I know this question might be closed as opinion-based, but what I need right now are some opinions supported by arguments. I am building an application with Postgres and Ecto (Elixir) as the ...
2
votes
4answers
885 views

Using a relational and a non-relational database in the same project?

I ran into a problem on the project I'm working on right now. It is basically an app that stores a path based on GPS coordinates. The app runs on android an saves the GPS location every second and ...
1
vote
1answer
116 views

Duplicating a key in a mapping table instead of joining

I am overviewing a structure where a mapping table of student enrollments is done. I am wondering whether an additional column (class_category_id) to the mapping table would become a problem or a ...
3
votes
1answer
61 views

How to storage history of a complex record

I have in my relational database three tables like this: Route -> RouteStore -> Store. So I can modify which stores can be part of a route every certain time (adding or removing) and I need to keep a ...
5
votes
1answer
403 views

Using key-value NoSQL DBs relationally

I am new to NoSQL and have always heard of "key-value" DBs like Cassandra or Riak and expected their tables to literally have two columns: a key-col and a value-col. I just started working at a new ...
4
votes
4answers
2k views

Database design for storing user groups based on filter criteria

We're designing a system to define user groups. The groups will be used to run queries, to target promotions at relevant users. For example, to send a "$5 Free if you come back this week" offer to ...
2
votes
5answers
395 views

Compromise between formal correctness and convenience in database relations

Suppose that you are modeling and application in which you'll have 4 kinds of entities, say a Person a Job a Machine a Chemical Now you have to model all possible relations (N:M) between those ...
2
votes
3answers
429 views

Database design for changing user roles

What database design would you suggest for the following example? Example: Imagine a bus with some people inside. Every person has some attributes like name, etc. Also there are driver- and passenger-...
1
vote
4answers
531 views

Best way to model “Composite Skus”?

I have a SKU record which keeps track of raw materials used, pricing, cost, etc. An example SKU would be a Hamburger (1 bun, 1 patty, 1 oz ketchup, costs $11 make, priced at $4, etc). I need to add ...
4
votes
3answers
2k views

Nested transaction management .NET

I have an asp.net core project that is divided into the following structure Controller Store Repository The controller receives requests, and calls the correct Stores that are responsible for ...
46
votes
7answers
2k views

What happened to database constraints?

When I review database models for RDBMS, I'm usually surprised to find little to no constraints (aside PK/FK). For instance, percentage is often stored in a column of type int (while tinyint would be ...
1
vote
3answers
193 views

Categorizing columns in a database table

I wanted to know how do people generically think of, in a data base table: Columns that people are expected to use mostly for grouping expectable of groupability. Columns that people are expected to ...
1
vote
0answers
36 views

Database Constraint from another table column

Let's consider the following objects : Article which can be of different types Equipment which point to an Article, some fields on equipment are only used for some types. I want to add constraint ...
18
votes
5answers
4k views

Reason to prefer RIGHT JOIN over LEFT JOIN

If I understand correctly, every RIGHT JOIN: SELECT Persons.*, Orders.* FROM Orders RIGHT JOIN Persons ON Orders.PersonID = Persons.ID can be expressed as a LEFT JOIN: SELECT Persons.*, Orders.* ...
0
votes
1answer
127 views

Best practices to require simultaneous write in storage objects

This question is on program architecture and relationship between DAL, Security and BLL in case, when simultaneous write in several storage objects is needed. I address it to Microsoft SQL Server ...
0
votes
3answers
108 views

How to split a table based on its child-types

We've a table which has a bunch of columns. Let's say it represents humans. Every human has a specific type: archer-human, car-human and so on. Everytime we create a row nearly all of the fields are ...
1
vote
3answers
289 views

Predefined city/country list vs manual entry for address table

I'm trying to design a database for my POS application and have stumbled when trying to create an address table. I'm confused whether to have city and country tables with predefined values and link ...
5
votes
3answers
1k views

How to enforce the mandatory participation constraint at the many end in a one-to-many relationship?

Say I have the following ER diagram: Enforcing the mandatory participation constraint at the one end is easy, I simply make the foreign key (school_id) in Student NOT NULL. But how can I enforce the ...
1
vote
2answers
58 views

Modify existing column or link to what the change is?

I am making a game. In this game people can modify a value of an object. Let's picture it like so. House is the object price is the value Mold is the modifier Should I reflect changes to the price ...
3
votes
1answer
6k views

Dashed line in Crow's Foot notation

I am learning about the Crow's Foot notation, and there is something that I don't understand, some tutorials shows an Identifying Relationship as having a straight line, and a Non-Identifying ...
14
votes
7answers
11k views

Disadvantages of using a nullable foreign key instead of creating an intersection table

Say I have the following ER diagram: Now if I represented the relationship using a foreign key of School in Student, I could have NULL values (because a Student is not required to belong to a School),...
2
votes
2answers
107 views

Keep consistency when items can be in several classes

I'm having a difficulty to figure out relating an item to classes properly. For example, I have a business directory website that shows profiles of different companies according to the products/...
0
votes
1answer
202 views

Handle database error for API server

Recently I worked with a Restful API server and encountered a problem with handling database error. The basic approach is every time a SQL exception occurred, the server will return "500" to client ...
60
votes
11answers
12k views

Should I define the relations between tables in the database or just in code?

In my experience, many of the projects I have read in the past didn't have relationship definitions in the database, instead they only defined them in the source code. So I'm wondering what are the ...
1
vote
2answers
133 views

Database Architecture - Comprehensive history of likes/shares/etc for all accounts?

The Setup This is a membership-based site. Each account is capable of: Making posts (unlimited, no restriction on frequency) Liking, sharing and commenting (on) the other posts The goal is to ...
4
votes
2answers
171 views

Why are dependencies typically reversed when representing complex objects as relational tables?

I'm going through some sort of phase where I over analyze and second guess every single decision I make when attempting to write software that has preventing me from getting anything done. I recently ...
4
votes
2answers
299 views

Why are first class polymorphic relations not possible in relational databases?

Lets say I want to setup a roles table that has a polymorphic relationship to resource. I understand that that I could directly setup a foreign key - by adding for example a roles.forum_id column. ...
1
vote
3answers
177 views

How can I model IS-A relationship on a ledger, when having different classes of products?

This is a question on how to model database entities and relationships when it comes to storing persistent data and dealing with differently-handled types of products. Use Case Company sells various ...
2
votes
1answer
131 views

Given an entity that can have a parent, or can stand on its own, what is the best way to represent it in a relational database

Lets assume we have to model the following entities: Application : Application submitted by users Application Study: the study to determine if the Application should be accepted Consultation : A ...
1
vote
2answers
559 views

How to structure and connect my “price” table to existing relational database table structure?

I have several tables: product, category, addon in various relational relationships. I structured it this way because: I have many products that belong to their own specific category. For each ...
5
votes
5answers
3k 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 ...
2
votes
2answers
77 views

Labeling the multiplicities in class-table mappings

Trying this again but with a slightly different approach. My previous thread was here Can anyone explain this one-to-one, one-to-many, many-to-one, many-to-many concept with respect to ORMs? but ...
4
votes
1answer
2k views

(Dis-)Advantages of storing objects in relational database?

In an off-hand comment on this question about storing PHP objects in a MySQL database I mentioned that it is probably not a good idea to do so. My reasoning is that MySQL is explicitly a relational ...
12
votes
4answers
860 views

Does it ever make sense NOT to condense one to one relationships?

If we have Table A that has a one to one relationship with Table B, does it ever make sense to keep them apart? Or does it never hurt to combine them into a single table? Do either of these scenarios (...
2
votes
1answer
3k views

Designing a database for a voting application

I'm working on a smallish voting webapp. Similar to strawpoll but only authenticated users are allowed to make polls, while everyone is allowed to vote. User: id email/password_hash/name/...some ...
7
votes
4answers
8k views

Can anyone explain this one-to-one, one-to-many, many-to-one, many-to-many concept with respect to ORMs?

A lot of ORM sites and SQL tutorials mention these relationships as if they are obvious or to be taken for granted, but I don't fully understand why the distinctions need to be made. Consider two ...
1
vote
2answers
129 views

How can I maintain a database record of an entity that belongs to several categories of another entity, per year, per season, etc

I am building a kind of School Management System. I want to be managing the database records of things like assessment, term/season, academic years. every info must belong to a particular academic ...
0
votes
1answer
118 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
4answers
157 views

What is a good convention for table key id name?

I'm wondering if there is any good convention for key attributes in relational datatables. ID or Id seems to be good. however it conflict with others ids when joining. MyTableNameId does not ...
3
votes
1answer
593 views

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

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 ...
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) ...
3
votes
4answers
6k views

How to simplify this Role and Permission management design?

I'm working on a web application that has users with multiple roles and each user can perform multiple operations, based on user's role, and the permission level the role has on the operation. I came ...
1
vote
1answer
447 views

ERD for a Lab Database

Question: In a database for a clinical reference lab a specimen, when taken, triggers a test order (for testing obviously) creation.If that test order produces a failure, the specimen is given a new ...
2
votes
1answer
432 views

Best approach to store huge amount of denormalized/dynamic data with relational database

Here I have a very basic ILS (Integrated Library System) done a long time ago (made with C#/SQL Server), in production for years. But now I have a demand of make this system MARC 21 standard compliant,...
1
vote
3answers
576 views

How can one think of a relational schema in terms of storing it in a NoSQL database?

Recently I've been wanting to experiment with NoSQL databases, especially document store ones. After reading, I still don't understand how one might model information that is contained in a relational ...