Questions tagged [relations]
The relations tag has no usage guidance.
123
questions
1
vote
1
answer
44
views
How to count the number of relationships to a table in MySQL?
I am arranging an ERD diagram of an existing database with about a 100 tables, to add some structure to the process, how rank out the number of relations the tables have to each other?
For example, ...
- 135
0
votes
0
answers
5
views
How to have Articles in Collections with default?
Using a relational database (MySQL), I have the following structure:
Articles
- id
- title
- slug
- content
- author_id
- ...
Collections
- id
- title
- parent_id # Points to a collection ...
- 113
3
votes
1
answer
55
views
How to "merge" rows along with their foreign many-to-many relations without violating unique constraints?
Fiddle: https://dbfiddle.uk/-JLFuIrN
Table
CREATE TABLE files (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE folders (
id bigint GENERATED ALWAYS AS IDENTITY ...
- 176
0
votes
1
answer
25
views
Store different data of the same format in one table, or multiple tables of the same format?
Consider I have the following MySQL tables:
products
id | created_at | updated_at | name | description | barcode
stores
id | created_at | updated_at | name | description | address
product_attributes
...
- 101
0
votes
1
answer
36
views
How can I transform an exclusive constraint relationship from ER to RM model?
Let say we have two entities TEACHER and COURSE. There are two relationships between them. A TEACHER can teaches a COURSE, and also can learn a COURSE, however, the TEACHER cannot teach and learn a ...
- 1
0
votes
0
answers
84
views
Create a student-grade query from a relational database
I have three tables:
Students (Id, name)
Assessments (id, name, max_score)
StudentAssessment (Id, student_ID, assessment_id, score) where ids are FK from the other two tables.
I think this is the most ...
- 103
0
votes
0
answers
160
views
Can an ER relationship have `NULL`able attributes?
Texts out there about the entity-relationship model seem to only mention NULLable attributes on entity types and not relationships. Is there a concrete saying whether such attributes are allowed to ...
2
votes
0
answers
100
views
My relational schema and a few questions about relations and duplicate values in columns
I'm trying to create database in which I could store thousands of job postings. I put here 5 sample records of these job postings so that you could better understand how everything looks like:
Now, ...
- 55
0
votes
1
answer
1k
views
System/database design for comments/replies and upvotes at scale
Recently I started discovering a topic around "How to design db schema for storing structures similar to Instagram/Facebook/Reddit comments?".
After extensive research, I was able to find a ...
0
votes
0
answers
96
views
How to organize optional cascading deletes of foreign key references (probably with triggers)
CREATE TABLE "t1" (
"id" UUID NOT NULL PRIMARY KEY,
"other" text
);
CREATE TABLE "t1_proto" (
"id" UUID references ("t1", "...
0
votes
1
answer
88
views
How to automaticaly create entries in a parent table before filling child table? What is that called in db terminology?
What I want is for inserting the string name of a publication in a child table to associate with the PK of the publication in the publications table. For example, let's say I have a table of counties ...
- 125
2
votes
3
answers
173
views
Database Design - Relationship between two or more junctions tables
I am a junior developer and I don't know how to implement this next relationship.
Note: All entities described here are fictional but have equivalents in an actual production Database. Performance is ...
- 23
0
votes
0
answers
94
views
Designing one to many relation where Many can be one of 2 entities
Apologies if this seems like a rather naive question but I'm relatively new to database design and I'm struggling to find the correct terminology to help source answers myself, so I'm turning here.
...
- 1
0
votes
1
answer
62
views
How to create a reference in a many-to-many relationship with transact SQL?
I am creating my first database using only code (I don't like to use graphical interfaces, I feel that I am not learning to write T-SQL code, it is my personal opinion), I do not know how to create ...
- 11
1
vote
1
answer
68
views
Terminology for Specific Kind of One-to-Many Relationship
This question regards how a one-to-many row might "belong" to a different row and what exactly to call it. I might be asking for a term that does not exist.
Please see the below two tables. ...
- 121
0
votes
0
answers
71
views
How to create a junction table for three many to many relationship?
I have three tabels named student, question and exam.
A question can be asked in multiple exam.
A student can appear in multiple exam.
A exam can be attended by multiple students.
And I need to ...
- 101
0
votes
0
answers
73
views
Optional int column for mysql table with 3m rows?
I have 3m rows and 20k of them need to have int value of column "y".
Should I make new column "y" with default NULL or 0 or making mess with new table and joining?
I've already ...
- 177
0
votes
0
answers
15
views
Should I create a linking table between two tables with existing IDs or add a column to an existing table
So I have two tables. Not with these exact columns but it's just to illustrate the point
users
id | forename | surname
forms
id | name
Using MS SQL and was wondering what's best practice to link a ...
- 194
0
votes
1
answer
55
views
Should I use JSON or related tables to represent a relation in Cassandra?
I know it is possible to store JSON in Cassandra, and I also know that Cassandra is a structured query language. Because of these two informations, I am unable to determine whether I should store some ...
- 103
0
votes
0
answers
20
views
Implementing notion/figma alike commenting feature
I'm building an application that creates offer templates. Each offer has different sections, (for eg; offerTeams, offerFeatures, ...)
A user can add a comment to any section in the page. The logic ...
0
votes
0
answers
16
views
Standard solution to join 3 related data-group
i have 3 data-group
stuffs - users - notifications
each stuff for each user has deferent notification state
and i dont know witch model is standard for connect them,
I do not want to get into trouble ...
1
vote
1
answer
393
views
referencing data from another table without using a join
Hi all so I have a homework assignment that I am having difficulty understanding. the problem I have been given is this:
ix. Referring to the customers and shipping_details tables on the ER, ...
- 11
2
votes
2
answers
134
views
foreign key table update impact
Lets say there is orders table with few foreign keys. One of them is addressId.
Orders
-- Id
-- orderNum
-- customerId
-- addressId (foreign)
CustomerAddress
-- Id
-- customerId
-- street
-- cityId
--...
-1
votes
1
answer
1k
views
Update all rows with a one to many relationship count in the same table in MYSQL
I am trying to update all rows of a MYSQL table with the total amount of referred users for each user. 1 user can refer multiple users and I need to run a MYSQL query to update the invite count (total ...
1
vote
1
answer
15
views
Creating relations that makes sense in a many 2 many 2 many format or is the solution badly designed?
I'm trying to get better at databases and started a hobby project for this.
So this is the flow I'm trying to achieve.
A user can have one or more portfolio
A portfolio belongs to that user
A ...
- 113
0
votes
1
answer
17
views
Convert generalization in ERD to Relationnal Database having no additional column
I've a quite simple problem but the litterature I came accross didn't seem to dwell on.
Let's say I have an entity OFFER which represents an Offer on a Product, for each product, one offer can be &...
1
vote
1
answer
39
views
Database Design for evolving entities
I am looking for some best practices regarding table design when an entity is expected to evolve over time and get more properties.
As an example let's consider a process that has a bunch of metadata ...
- 11
1
vote
0
answers
34
views
How to restrict allowed sub-entities in table referencing super-entity based on a type code
I'm working on my first db architecture for a platform containing several user types and that can exchange assets between them. There are also several transaction types, but each transaction type ...
- 111
0
votes
1
answer
107
views
What database & design would you use for legos [closed]
I'm trying to conceptualize an interesting problem I am designing for. Using lego's (the plastic construction toys) as an analogy seems to work well. If this is a fairly recognizable problem, then I'd ...
- 103
0
votes
3
answers
50
views
Best Practice to define has many through relationship
Given the Images. Sometimes I needed to fetch lessons related to a course without joining the topic table.
It is a good way to model the Schema in the diagram at the bottom?
Kindly advice.
0
votes
1
answer
655
views
How return list with inner list in SQL? [duplicate]
I prepared following SQL statement:
SELECT tasks.name, profiles.email, types.specification,
tasks.description, tasks.attachment_link, tasks.priority,
assigned_tasks.progress_details, ...
- 3
1
vote
1
answer
292
views
ER Schema - bank system
Hello i'm trying to model a simple banking scheme
where I have 3 transactions: withdraw, transfer, deposit.
of these 3 operations, the transfer is the only one that needs more attributes, I need to ...
- 113
0
votes
1
answer
52
views
Cardinality between tables in the present or over time?
I have a question regarding cardinality between two tables.
I'm not sure how to approach it.
Imagine the following:
There are groups: group 1, group 2, group 3 etc
One group can have multiple items ...
- 135
1
vote
1
answer
38
views
Need a better approach in how to design this relation
I have a table fluxo, and fluxo have many fluxo_acao, but a entry in fluxo_acao can have three types, and each type have they particularities. to acomplish this, i created more three tables(...
- 113
1
vote
1
answer
190
views
Relationship design for areas, managers and general manager
Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also ...
- 13
0
votes
2
answers
507
views
database management system vs database vs tables vs file [duplicate]
I have been trying to understand the way a database system works. Digging through the internet, I have come to following picture.
Database system is required because we need a way to store data, ...
- 3
0
votes
1
answer
508
views
Whats the right table relationship for these entities?
I want to store all the data for the following entities:
Users
Projects
Roles
A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each ...
- 103
0
votes
1
answer
112
views
What are the main disadvantages of adding relationship between tables at later date?
During early development I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.
I tend to forget mapping the relationship between ...
- 113
1
vote
1
answer
277
views
Modeling an either/or relationship
I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship.
Say I have an ...
- 111
0
votes
2
answers
39
views
Is there a good reason to create an additional table to realize a 1:N relation?
I have the following tables:
Users
_____
UserID
Data
...
Roles
_____
RoleID
Data
...
UsersRoles
_____
UserID
RoleID
Users and Roles are in a M:N relationship. As such I have the UsersRoles table to ...
- 3
1
vote
1
answer
686
views
ER Diagram | Group, Post, User relationship
I need to design an ER diagram, but I am confused in a section, more specifically how to relate the post with the groups, because I need that post contains the information of in which group I publish ...
- 111
1
vote
1
answer
55
views
Should I capture all the relationships in a Database design?
I am a newbie when it comes to database design. I was trying to create a blogging application and I ran into a problem. My database consists of the following tables:
User
HasInt (Records which user ...
- 13
1
vote
0
answers
294
views
Should I use Postgres' JSON columns for additional related data of different types and not yet known?
I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys.
The tbl_main is the most frequently written to table, as in it receives new ...
- 111
1
vote
1
answer
214
views
Understanding linking tables in a many-to-many relationship
I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may ...
- 29
1
vote
1
answer
2k
views
How to filter multiple many to many relationship based on multiple tags?
I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like:
Problem table:
----+---------------------------...
- 113
1
vote
3
answers
135
views
Putting foreign keys of several tables into the same column
I've never come across this situation, not sure how to go about it.
Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and ...
- 31
1
vote
2
answers
172
views
Modeling a three-way association with optional relation
Business rules
I have three tables (Parties, Categories and Products) which representing the following relationships:
A product is classified by zero-one-to-many categories
A category classifies ...
- 33
0
votes
0
answers
25
views
How do I restrict relations between entities to a parent entity?
I have a multi-vendor database for my e-commerce application. Below is a simple interpretation of my table and relation structure.
Table: stores
store_id (PK, AutoIncrement)
Table: categories
...
- 3
0
votes
1
answer
1k
views
Get data from different tables indexed by same ID
Database: postgresql 12. I have a table people:
person_id | name
----------+-----
1 |John
2 |Paul
3 |Jim
...
Each person can have zero or more car insurance contracts, so I ...
- 57
0
votes
2
answers
114
views
Designing One-To-One Relationships
To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different ...
- 103