Skip to main content

Questions tagged [relations]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
54 votes
2 answers
13k views

Is it a bad practice to have several mutually exclusive one-to-one relationships?

Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc. Is there anything ...
Arthur Tarasov's user avatar
15 votes
1 answer
6k views

Database design for products with bundles of products

I am building a database system for my retail business. I have set some tables which are: Product Purchase Sales Balance All are connected one another and are able to show my inventory level. The ...
shandery's user avatar
  • 151
6 votes
1 answer
49k views

Structure a database for a Blog

I built a database for a blog. I am a student yet, so my knowledge is limited in this area. I post this question to get a briefly description of what I do wrong, why, and how I can fix it. I'm not a ...
user avatar
5 votes
3 answers
12k views

How do I read ERD Notation (crow's feet) to convert to Natural Language?

Background I am attempting to insure I understand how to read ERD (Entity Relationship Diagram) notation so I can convert it to a natural language explanation. I'm trying to insure I can explain a ...
raddevus's user avatar
  • 201
5 votes
1 answer
2k views

Generate fake data for several tables with relationships

I have 3 tables where I want to generate fake data in UsersCategoryLink. How can I insert in the table UserCategoryLink the column UserID with random users from the table User and a random id from ...
H. Pauwelyn's user avatar
5 votes
3 answers
88 views

Constraining data based on related data in a different table. Should this be done on the database level or the application level?

I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those ...
Zachary Weber's user avatar
4 votes
5 answers
1k views

What is the best practice for selecting from two tables with multiple relations?

I have two tables with a structure like below: and am selecting the data with below query: SELECT (SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN, (SELECT Name FROM ...
Nima Derakhshanjan's user avatar
4 votes
1 answer
3k views

What is the definition of bad circular dependency?

I have been searching for while for a good resource, where the circular dependency is well explained, unfortunately nothing good found. Therefore I tried to understand exactly which kind of circular ...
Mohammed Noureldin's user avatar
4 votes
1 answer
6k views

Unique key based on many-to-many pivot table

I have to manage artists and albums tables: | artists | | albums | | album_artist | +--------------+ +--------------+ +--------------+ | id | | id | | id ...
user197675's user avatar
4 votes
1 answer
4k views

How can I avoid this circular relation?

I based my account/user table design following this article. So I have set my table relationships to look something like this: User <- Memberhip -> Group -> Account With this design, a user ...
Vic's user avatar
  • 165
3 votes
5 answers
1k views

Database: One table or many

I am working on a project of classified ads site. I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories. ...
Gammer's user avatar
  • 133
3 votes
2 answers
1k views

Is the inverse of Armstrong's Axioms valid as well?

I have been looking into Armstrong's axioms a little bit. In a homework[1] exercise I was asked to prove A→G is in F-closure. I managed to get it at this point: AB → GB Being at this point, can I ...
johndoe123's user avatar
3 votes
1 answer
2k views

Proof that Inner Join is Commutative

I'm trying to prove to myself that the order of inner joins doesn't matter, but, in an abstract sense, I'm coming up with nothing. How would one go about proving that the order of a set of INNER ...
joe55's user avatar
  • 33
3 votes
1 answer
222 views

LOB in frequently queried table or separate table

I have a table named FinTrans in an Microsoft SQL Server 2012 server with approx 10K-30K records at any given time and about 2K records are inserted and deleted each day. The table has 3 nvarchar(...
Daniel's user avatar
  • 143
3 votes
3 answers
110 views

Meta-relationships (Relationships between relationships) versus tuples

I am designing a database which needs to store some fairly complex relationships among many different types of entities. In the simplest example, let us say I have three types of entities - "Student",...
alexw's user avatar
  • 192
3 votes
1 answer
42 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
Willem-Aart's user avatar
3 votes
1 answer
1k views

Mysqldump subset of database with relational data using a master table

I have found a lot of articles describing the concept of exporting and importing relational data, but I have not been able to find any article describing this more generally for a MySQL database, ...
user3390352's user avatar
3 votes
1 answer
213 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
3 votes
0 answers
7k views

How do you perform the chase test?

Trying to follow the chase algorithm as I understand it, If two rows agree in the left side of a functional dependency (FD for brevity), make their right sides agree too. Always replace a ...
SS''s user avatar
  • 155
2 votes
2 answers
317 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
2 votes
1 answer
4k views

Is it good practice to create a separate table for views count?

I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for ...
DojoDev's user avatar
  • 43
2 votes
2 answers
842 views

Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
Hadi's user avatar
  • 127
2 votes
1 answer
1k views

Functional dependencies proving method

I was having some difficulties when trying to use Armstrong axiom to solve functional dependencies. R (A,B,C,D,E,F) Given FD = { A->CD , C->BE, B->F, F->A } Prove A-> BF By using ...
QWERTY's user avatar
  • 121
2 votes
1 answer
2k views

What is the cardinality of entity relationship between Employee and Department?

Recently, a friend of mine asked me this question about the cardinality between a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet ...
nuux's user avatar
  • 123
2 votes
1 answer
507 views

BCNF decomposition does not prevent insert anomaly?

I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture. Requirements A poll has a unique id (poll_id), a ...
taffer's user avatar
  • 269
2 votes
1 answer
827 views

Database relationship inheritance [closed]

I have a vehicle sales store. In my store I sale many kind of vehicles for example cars and boats. And I would like to keep track of the sales. My vehicles has some attributes as: Arrive on store ...
Lin's user avatar
  • 123
2 votes
1 answer
74 views

Equivalent functional dependencies

I cannot understand why F1 is not equivalent to the other sets of dependencies below given the relation R(A,B,C): F1 = {A → B, B → C} F2 = {A → B, A → C} F3 = {A → B, AB → C} This is question no. 6 ...
 Enq19's user avatar
  • 29
2 votes
3 answers
567 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
2 votes
3 answers
2k views

Contacts table with multiple entries?

Here is a part of our Database: Table: Users ID INT First Name VARCHAR Last Name VARCHAR Email Address VARCHAR ... Table: Contacts ID INT User_id INT (Foreign Key)...
Raghav's user avatar
  • 21
2 votes
1 answer
419 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
wiltomap's user avatar
  • 317
2 votes
1 answer
515 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image I'm ...
nextstep's user avatar
  • 123
2 votes
0 answers
22 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
2 votes
0 answers
204 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
2 votes
1 answer
458 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
2 votes
0 answers
458 views

A multiple choice question, with multiple or only one correct answer, and sometimes no correct answer [closed]

It's a question & answer based model, and here is the scenario: A question with multiple choices given, with 3 to 7 correct answers among those choices. A question with multiple choices given, ...
Arslan Ali's user avatar
2 votes
1 answer
1k views

Conditional relationships? One-to-many relationship using a pivot table for only a type of entity

I have the following tables: users: - id - name - user_type_id (references id on user_types) neighborhoods: - id - name homes: - id - house_number - neighborhood_id (references id ...
Vic's user avatar
  • 165
2 votes
0 answers
3k views

Review the ER diagram regarding a National Football Team

We need to store information pertaining to football players who are or were members of the national team. For each player we store the following: first name, last name, date of birth, debut ...
user avatar
1 vote
2 answers
873 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
1 vote
2 answers
9k views

Counting distinct entries in a column using relational algebra

So I have a table similar to this one. Each user has posted a review about one or more hotels(A,B,C,D) but on different dates so there are no duplicate tuples even though a person might have reviewed ...
Sreten Jocić's user avatar
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
1 vote
2 answers
233 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
1 vote
1 answer
434 views

Should pivot tables have more columns (many to many relationship)

I'm currently thinking about how to organize my tables, and I have some information that I'm not sure were should be, one of them is the quantity of a food in a refrigerator Is it right if I add ...
Felipe Moreno Borges's user avatar
1 vote
2 answers
258 views

Unrelated tables and time intervals

I am going to comment on the scenario that I currently have when designing my database. I have the following tables: user (id, name, lastname) role (id, name) user_role (user_id, role_id, ...
Mr. Mars's user avatar
1 vote
1 answer
2k views

Circular dependency between 2 table with one to one and one to many relationship

I have 2 tables in SQL database, User and Company. User belongs to one company. So user table has UserID & CompanyID (foreign key) Company can have multiple users. One of them will be the ...
Developer's user avatar
1 vote
1 answer
1k views

Difference of sets with different amount of columns?

Problem When there exists two sets, R and S, and we take the difference R - S, we remove the elements from R that exist in S. For cases where the number of columns are the same on both sides, such ...
isakbob's user avatar
  • 217
1 vote
1 answer
66 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
1 vote
1 answer
271 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
1 vote
1 answer
83 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
1 vote
1 answer
48 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
1 answer
414 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