Timeline for How to store a (record which holds a) reference to any other column (attribute) in another table (relation)
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 11, 2020 at 10:18 | comment | added | Vesa Karjalainen | Instead of hstore, I'd go to JSONB | |
| Aug 11, 2020 at 9:51 | history | edited | Claudio Floreani | CC BY-SA 4.0 |
added 55 characters in body
|
| Aug 11, 2020 at 9:29 | comment | added | Claudio Floreani | @Lennart A table name change have a minimal impact on the database integrity, because usually there are not reference to this name into the data themselves and only few references in the database schema, and you could easily search/replace that name in the application code. | |
| Aug 11, 2020 at 9:25 | comment | added | Claudio Floreani | @AlbertGodfrind please check also the revised answer. | |
| Aug 11, 2020 at 8:51 | history | edited | Claudio Floreani | CC BY-SA 4.0 |
Explained problems with the first attempted solution and added another solution which addresses them.
|
| Aug 11, 2020 at 8:43 | comment | added | Lennart - Slava Ukraini | What do you do when table name changes? What you describe is some kind of EAV, you may want to read up on that so that you understand the consequenses of your design. | |
| Aug 11, 2020 at 8:08 | comment | added | Albert Godfrind | The only way to make the code totally flexible and make it automatically adapt to the data model is to use dynamic SQL. That introduces another level of complexity to your applications. Unless you hide that behind a layer of stored procedures and dynamic cursors. But even that will mean changing your applications: at the minimum, the host variables in which the results are written. Or the forms used by those applications. Or the web pages/reports updated by those applications ... | |
| Aug 11, 2020 at 8:05 | comment | added | Albert Godfrind | Unfortunately any code you write (like in Lennart's proposed answer) WILL have to know about the number and names of the columns. Say you add one more type of discount: you will need to change all that code. | |
| Aug 10, 2020 at 21:04 | comment | added | Claudio Floreani | @Lennart The advantage is that whenever column names are changed, you don’t have to search for and replace all occurrences and adjust all constraints and references for that name. If you wrote validation for the column names in the application logic, you have to change those too whenever the columns are renamed. Instead when using a type renaming has no impact, and a simple exception in the application logic is sufficient even if a column is removed. Consider that I’ve given an extremely simplified example, but the DB grows and could have more types and tables dealing with those columns. | |
| Aug 10, 2020 at 20:01 | comment | added | Lennart - Slava Ukraini | What advantage do you see with this approach? What would the query look like to produce the expected result in your question? | |
| Aug 10, 2020 at 19:06 | history | answered | Claudio Floreani | CC BY-SA 4.0 |