Strictly speaking, Postgres did not have stored procedures as defined in the ISO/IEC standard before version 11. The term is often used incorrectly to refer to functions, which provide much of the same functionality (and more) as other RDBMS provide with “stored procedures”. The main difference being transaction handling. What are the differences between “Stored … Read more
As always: there is no best solution. Each solution makes different things easier or harder. The right solution for you depends on which operation you will do most. Naive Approach with parent-id: Pros: Easy to implement Easy to move a big subtree to another parent Insert is cheap Needed Fields directly accessible in SQL Cons: … Read more
Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed.
Definitely Relational. Unlimited flexibility and expansion. Two corrections, both in concept and application, followed by an elevation. Correction It is not “filtering out the un-needed data”; it is selecting only the needed data. Yes, of course, if you have an Index to support the columns identified in the WHERE clause, it is very fast, and … Read more
Anders, do not give up any integrity or hardness, eg type safety. (Response coming). @Anders. No, not at all, subtyping is fine (the question is which form you use and what are the dis/advantages). Do not give up any strength or Integrity or type safety or checks or DRI. The form you choose will demand … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more
You could have a design like: +—————+ +——————-+ | PRODUCTS |—–< PRODUCT_VARIANTS | +—————+ +——————-+ | #product_id | | #product_id | | product_name | | #variant_id | +—————+ | sku_id | | +——————-+ | | +——–^——–+ +——–^——–+ | PRODUCT_OPTIONS |—–< VARIANT_VALUES | +—————–+ +—————–+ | #product_id | | #product_id | | #option_id | | #variant_id … Read more
None of that is necessary, especially not the doubling up the tables. That is pure insanity. Introduction Since the Standard for Modelling Relational Databases (IDEF1X) has been in common use for over 25 years (at least in the high quality, high performance end of the market), I use that terminology. Date & Darwen, despite1 consistent … Read more
Your case looks like an instance of class/subclass. There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages. One way is called “Single Table Inheritance”. In this design there is just one table for all types of users. If a given column doesn’t pertain to a given … Read more
1 to 0..1 The “1 to 0..1” between super and sub-classes is used as a part of “all classes in separate tables” strategy for implementing inheritance. A “1 to 0..1” can be represented in a single table with “0..1” portion covered by NULL-able fields. However, if the relationship is mostly “1 to 0” with only … Read more