Linked Questions

73
votes
5answers
27k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
86
votes
3answers
30k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
24
votes
3answers
5k views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address ...
14
votes
2answers
9k views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
16
votes
2answers
13k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
10
votes
3answers
5k views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People -----...
4
votes
1answer
339 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
1
vote
1answer
561 views

Make a boolean column unique per group

I want to ensure consistency and make sure only one phone line is the default for a house. Schema: PhoneLine ID | isDefault | HouseID I want to add a trigger so that when you add/update a row, and ...
3
votes
1answer
387 views

Best database design for payments with multiple constraints

I have to register payments. There are two things to pay. thingC or thingD (they may not be paid). You can only pay one thingD per payment transaction. You can pay many thingC per payment transaction....
1
vote
1answer
466 views

A question about foreign keys and many to many relationships

My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
0
votes
3answers
100 views

Model multiple 1-m relationship to a single entity

I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more. So far I can think of ...