Questions tagged [polymorphic-associations]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
545 views

Modeling Polymorphic Relations in Postgres 15 with bi-directional (cyclic) FK constraints

I am drawn to this design like a moth to a flame. I've read rumblings that cyclic foreign keys are a nightmare. Generally, I can see why they should be avoided. In this particular case, however, I don'...
untitled90's user avatar
2 votes
1 answer
58 views

Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks

I have the following database schema (MySQL 5.7): I have assistants that may be linked to either: one customer (might evolve to multiple customers in the future, hence the association table) one or ...
Merinorus's user avatar
0 votes
1 answer
37 views

Model types of an entity [duplicate]

Let's take a simple users table. I have a few different type of users. Let's simplify it into two, employees and customers, for the sake of the example, but keep in mind I do have much more. So I have ...
Jeremy Belolo's user avatar
0 votes
1 answer
526 views

Insert into table with polymorphic association in database

I want to design a database where some tables have multiple foreign keys. I am looking for the best design approach. I found out that this kind of problem is called "polymorphic association" ...
Alireza Azadi's user avatar
0 votes
0 answers
817 views

DB design - logging transactions

I am trying to implement a booking system for charging stations that can either extend or refund a booking. I basically have the following tables: booking booking_logs payment refunds With how the ...
ellaRT's user avatar
  • 145
-1 votes
1 answer
100 views

What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of ...
Ali's user avatar
  • 103
1 vote
1 answer
10k 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 ...
M.Templeman's user avatar
-1 votes
2 answers
281 views

payment table in relational datase

I have to model a database table for payments. These need to have two fields payment from and payment to and this needs to link to two tables one is clientA and clientB as in the representation below. ...
Blue Moon's user avatar
  • 143
2 votes
2 answers
4k views

How to model a relationship with variable tables (polymorphism)?

What is the best way to model the following kind relationship? Let's say I have tables representing two types of entities: company and person. I have a customer table with an entity_id field, which ...
JBentley's user avatar
  • 251
3 votes
0 answers
497 views

Database Designs Alternative to Polymorphism in Establishing Relationships

I'm working on a database which handles replacement of parts in a machine. Rules A machine has many components A machine has a set of assembly instructions Many components have many part (screws, ...
GeraldBiggs's user avatar
5 votes
2 answers
13k views

Best practise for polymorphic associations in MySQL

I have a MySQL database with 3 tables holding the main classes of data: companies (company_id) persons (person_id, company_id) loans (loan_id, company_id) Both a 'loan' and a 'person' belong ...
ratherBeKiting's user avatar
2 votes
3 answers
3k views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
oucil's user avatar
  • 506
2 votes
2 answers
6k views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
user99992934's user avatar
2 votes
1 answer
603 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
RNanoware's user avatar
  • 123
1 vote
0 answers
258 views

Do supertables produce bottlenecks?

There are several alternatives to using polymorphic associations and one of them is to use the so-called "concrete supertable". From my point of view, this is the neatest solution and is pretty easy ...
Mark Hardy's user avatar
15 votes
1 answer
8k views

Enforcing constraints "two tables away"

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short for ...
Snowball's user avatar
  • 327
5 votes
2 answers
403 views

Is there a well-known name for this "poor man's ref. integrity" schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin ...
starlocke's user avatar
  • 191
5 votes
1 answer
4k views

Polymorphic Association - is it bad?

In the following schema: Collections Upvotes Reports Upvotes Reviews Upvotes I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ...
RobVious's user avatar
  • 151
3 votes
1 answer
498 views

How to design database for polymorphic relationships

I have the following scenerio: There are some categories Each category has attributes An attribute could be of free text or collection of options There is a product which is assigned to category ...
Zaheer Abbass's user avatar
1 vote
2 answers
8k views

Foreign key with multiple references

I have the following three tables in mysql database named "THE_COLLEGE" mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
dinesh.mic's user avatar