Skip to main content
The 2024 Developer Survey results are live! See the results
75 votes
Accepted

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

The different types of cars are an instance of a general problem that surfaces over and over again in data modeling. It is called "generalization/specialization" in ER modeling, and "superclass/...
Walter Mitty's user avatar
  • 4,356
14 votes

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

There is nothing wrong with having as many entity sub-types in your model as are needed to reflect the reality of the data that you're trying to model. The question isn't whether sub-types are a bad ...
Joel Brown's user avatar
  • 12.5k
9 votes
Accepted

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

Create a covering index on Id column and Include Name on the table Management_EnumerableItem. CREATE INDEX IDX_Name on Management_EnumerableItem (Id) Include (Name) with (fillfactor=90); and write ...
Rajesh Ranjan's user avatar
6 votes
Accepted

Proof that Inner Join is Commutative

An inner join is the subset of rows from the cartesian product where a certain condition is true. Although the cartesian product is not commutative (nor associative), it is with regard to relational ...
joanolo's user avatar
  • 13.5k
5 votes

What is the definition of bad circular dependency?

There is nothing circular in any of these cases. In "circular reference" scenario you have "a chicken and an egg problem" -- can not insert a row into a table because it is always missing a reference ...
Damir Sudarevic's user avatar
5 votes
Accepted

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

I'd like to answer this more definitively now using the following image (from https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning) Using the definitions of these symbols you can see that ...
raddevus's user avatar
  • 201
5 votes
Accepted

LOB in frequently queried table or separate table

is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is? Leave it. That's tiny, and not worth adding complexity....
David Browne - Microsoft's user avatar
5 votes
Accepted

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

At a mere 10 updates per second, this is a non-issue. When you get to 100/sec, and are still using HDD, then we can discuss further. Or 1000/sec with SDD. At higher rates, yes, violate the textbook ...
Rick James's user avatar
  • 78.6k
4 votes
Accepted

Structure a database for a Blog

Tips: If an avatar is an image, use BLOB, not TEXT. Don't prefix each column name with the table name; it clutters unnecessarily. You are unlikely to ever need BIGINT; use INT UNSIGNED instead (max ...
Rick James's user avatar
  • 78.6k
4 votes

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

Please consider trying out this query, and let me know how it performs. WITH EnumerableItemNames AS ( SELECT PcsID = pcs.ID, v.ColName, ItemName = me.Name FROM dbo....
ErikE's user avatar
  • 4,315
4 votes

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

No, that is not true. Armstrong's axioms are a sound and complete axiomatization of the logical implication for functional dependencies. Here is a relation, where the functional dependency XZ → YZ ...
miracle173's user avatar
  • 7,735
4 votes
Accepted

foreign key table update impact

I would maintain two address tables. Not an address per order. These would be... CustomerAddress - would always be their home address. ShippingAddress - This could be anywhere they would like ...
Brendan McCaffrey's user avatar
4 votes

System/database design for comments/replies and upvotes at scale

This question is likely to generate a lot of opinions. In comes the "NoSQL" crowd touting in vague terms why relational is bad and this and that. The issue is complicated because the ...
Gunther Schadow's user avatar
3 votes
Accepted

Counting distinct entries in a column using relational algebra

Besides the more compact syntax (from @McNets' answer): select User, count(distinct Hotel_Reviewed) HotelsReviewed from InitialRelation group by User; we can also do a projection ...
ypercubeᵀᴹ's user avatar
3 votes

Functional dependencies proving method

Here is a possible derivation: 1. A → CD (given) 2. C → BE (given) 3. CD → BED (by augmentation of 2 with D) 4. A → BED (by transitivity of 1 and 3) 5. B → F (given) 6. B → BF (by ...
Renzo's user avatar
  • 4,080
3 votes
Accepted

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

What is the cardinality of (this) entity relationship between Employee and Department? Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two ...
ypercubeᵀᴹ's user avatar
3 votes
Accepted

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

No, the inverse is not true. The assumption: if XZ → YZ then X → Y is false. You could have only XZ → Y for example, from which you can infer XZ → YZ and a few others. But not X → Y.
ypercubeᵀᴹ's user avatar
2 votes
Accepted

In a school database, do I need to have repeating data when a student can also be a teacher?

What you are saying makes sense, yes. One thing is that you actually can't select students without excluding Persons that are teachers... And even if you did, the students that are also teachers ...
Magier's user avatar
  • 4,797
2 votes
Accepted

Postgres relationship statement

To start to answer this question, I created 4 tables (see the end of the post for sample data - or better still, see the fiddle here): CREATE TABLE parent (parent_id INTEGER, parent_name VARCHAR(20)); ...
Vérace's user avatar
  • 30.3k
2 votes

Equivalent functional dependencies

the answer provided at the bottom of that page states that F1 is not equivalent. That is correct. F2 and F3 are equivalent but F1 is not. F2 is equivalent to F3 because from A → C we can infer that ...
ypercubeᵀᴹ's user avatar
2 votes

One to Many Relationship: Describing the Tables

The typical nomenclature for this type of table design would be "class-subclass". In your example of Books and Authors, Books is a subclass of the Author class. Any book automatically has an author, ...
Hannah Vernon's user avatar
  • 70.2k
2 votes
Accepted

Database relationship inheritance

Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start. A vehicle can be either a boat or a car. A good place to start is to think ...
Lennart - Slava Ukraini's user avatar
2 votes

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

In SQL Server, I might accomplish this using a cross reference table between "companies" and "users" where only a single row can be an owner for a given company, via the presence of a unique non-...
Hannah Vernon's user avatar
  • 70.2k
2 votes
Accepted

Difference of sets with different amount of columns?

If I recall, the difference operation requires the two relations involved to be compatible, implying that they should have the same number of columns. Quoting from Wikipedia about relational algebra ...
tinlyx's user avatar
  • 3,620
2 votes

BCNF decomposition does not prevent insert anomaly?

Edited I think that the relation is in BCNF, but it is known that this fact does not always solve all the anomalies. For this reason other normal forms, like 4NF, 5NF, etc., for instance those based ...
Renzo's user avatar
  • 4,080
2 votes

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

Those caches are per connection. They are not held in shared memory.
jjanes's user avatar
  • 40.4k
2 votes

Database model / design best practice

I would say that if the company locations and non-company locations store a fair amount of different data, then to avoid a bunch of NULLS I would have two tables, and let the application insert data ...
John Herbert's user avatar
2 votes
Accepted

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

There is no restrictions on data stored in the pivot table. The simplest case of two IDs establishes only qualitative relations between fridges and foods: this food is stored in this fridge. If you ...
Kondybas's user avatar
  • 4,556
2 votes

What are the main disadvantages of adding relationship between tables at later date?

is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"? Short answer: No. Very little you can do to your database is "...
Phill  W.'s user avatar
  • 8,915

Only top scored, non community-wiki answers of a minimum length are eligible