Questions tagged [relations]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2 votes
0 answers
21 views

Is there an algorithmic way to identify potential primary key foreign key relationships in postgres

I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair....
Ashwin Prasad's user avatar
0 votes
1 answer
32 views

Possible options for SQL and NoSQL combination for social media data

I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we ...
dksr's user avatar
  • 101
1 vote
1 answer
50 views

How can I SELECT columns from one table that corresponds with a column of another table on JOIN without creating a relation between the tables?

I have 3 tables: CREATE TABLE catalog.shoes ( shoe_id SERIAL PRIMARY KEY, brand VARCHAR(255) NOT NULL, model VARCHAR(255) NOT NULL, price INT NOT NULL, CONSTRAINT shoe_name UNIQUE (...
ntruter42's user avatar
  • 121
0 votes
0 answers
33 views

Noobie needs help on designing database for membership management tool

I have searched through some of the database examples, but have yet to wrap my head around the logic of the relations between databases, so appreciate some support. Here is what I am trying to do: for ...
mor3dr3ad's user avatar
0 votes
1 answer
181 views

What is the most straightforward way to create subtypes and supertypes at the same time?

I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?: Create a supertable ...
Logos Masters's user avatar
0 votes
0 answers
37 views

Drawbacks of using a single table to hold all relations

Say I have a simple user table that has some unique columns like id and email, and a "level" column that defines the level of the user, such as admin, or moderator. Level is a fixed set of ...
Alex's user avatar
  • 165
0 votes
2 answers
173 views

How to join multiple many to many tables

I have tables - games, game_views, game_views_game_links, genres, games_genres_links. I need to count views from game_views, filter by them and join genres table to genres field. If i use only 2 joins ...
Nark Ebavich's user avatar
1 vote
1 answer
234 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, ...
OrigamiEye's user avatar
3 votes
1 answer
200 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 ...
Biller Builder's user avatar
0 votes
1 answer
79 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 ...
user2190492's user avatar
0 votes
1 answer
49 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 ...
xerez's user avatar
  • 1
0 votes
0 answers
242 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 ...
MartynW's user avatar
  • 103
2 votes
0 answers
184 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, ...
beginsql's user avatar
0 votes
1 answer
4k 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 ...
IDobrodushniy's user avatar
0 votes
1 answer
269 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 ...
Stonecraft's user avatar
2 votes
3 answers
510 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 ...
Codingwiz's user avatar
  • 123
0 votes
0 answers
107 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. ...
Niz's user avatar
  • 1
0 votes
1 answer
109 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 ...
I'm newbie's user avatar
1 vote
1 answer
80 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. ...
user1325179's user avatar
0 votes
0 answers
30 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 ...
pee2pee's user avatar
  • 194
0 votes
1 answer
214 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 ...
papillon's user avatar
  • 103
1 vote
1 answer
1k 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, ...
Samuel Ross's user avatar
2 votes
2 answers
290 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 --...
Abdusalam Ben Haj's user avatar
-1 votes
1 answer
2k 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 ...
webmaster8800's user avatar
1 vote
1 answer
17 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 ...
Dansekongen's user avatar
0 votes
1 answer
18 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 &...
nprime496's user avatar
1 vote
1 answer
46 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 ...
Philipp Just's user avatar
1 vote
0 answers
63 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 ...
Matheus Leão's user avatar
0 votes
1 answer
123 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 ...
Michael Ramos's user avatar
0 votes
3 answers
61 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.
Basit Tope's user avatar
0 votes
1 answer
1k 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, ...
Cecherz's user avatar
1 vote
1 answer
391 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 ...
Ming's user avatar
  • 123
0 votes
1 answer
72 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 ...
Mister Equis's user avatar
1 vote
1 answer
42 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(...
André Walker's user avatar
1 vote
1 answer
270 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 ...
luisepa1420's user avatar
1 vote
2 answers
821 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, ...
mw981's user avatar
  • 13
0 votes
1 answer
667 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 ...
Samu R's user avatar
  • 103
0 votes
1 answer
167 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 ...
encryptoferia's user avatar
2 votes
1 answer
433 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 ...
Amarth Gûl's user avatar
0 votes
2 answers
53 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 ...
Alpha's user avatar
  • 3
1 vote
1 answer
939 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 ...
ontimond's user avatar
  • 111
1 vote
1 answer
84 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 ...
ShayakSarkar's user avatar
1 vote
0 answers
335 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 ...
kmindi's user avatar
  • 111
1 vote
1 answer
278 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 ...
zv426's user avatar
  • 29
1 vote
1 answer
3k 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: ----+---------------------------...
Masked Man's user avatar
1 vote
3 answers
178 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 ...
George Well's user avatar
1 vote
2 answers
219 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 ...
Jonah's user avatar
  • 33
0 votes
0 answers
28 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 ...
Owen's user avatar
  • 3
0 votes
1 answer
2k 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 ...
homer5439's user avatar
0 votes
2 answers
142 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 ...
NickDelta's user avatar
  • 113