14

It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already. Preliminary data model and assumed business rules I defined a list ...


12

"date" Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like: CREATE TABLE test ( id serial PRIMARY KEY , ts timestamp NOT NULL -- also adding NOT NULL ...


12

In addition to what @Josh already clarified, this quote from the manual should answer the question in the title: [...] an inheritance link can be removed from a child using the NO INHERIT variant of ALTER TABLE. ALTER TABLE child NO INHERIT parent; Either that or just delete the child table. Also, you can include all inheriting tables when dropping ...


10

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves. Anyway, the solution would look something like this: MERGE INTO Table1 AS t1 USING MyTable ON 1=0 -- always generates "...


8

Inheritance is one of those features that I wouldn't touch. AFAIK, it's used internally for replication and partitioning in some capacity. I'm not sure if it was even designed with the intent to be used by the end-user. Concrete Technical Drawbacks Drawbacks on UNIQUE and REFERENCES The docs covers some of the drawbacks in the CAVEAT section (below is ...


6

If you are using a recent version of PostgreSQL (i.e. 9.1 or later), please do the following: log into your database using the psql command-line client \d+ <tablename> ... where <tablename> is the name of your parent table. This will give you a list of tables which inherit from your parent table at the bottom of the output. There is no such ...


6

The mentioned deficiencies are not a reason not to use inheritance! Inheritance works here similar to Class-Inheritance with independent Objects. You may have a class/table 'fruits' and a class/table 'apples' and 'oranges'. Since apples and oranges inherit their meta definitions from fruits, you can fetch them via fruits. However they are independent classes,...


5

As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!): SELECT * FROM ONLY parent NATURAL FULL JOIN child; Since NATURAL is (per documentation): ... shorthand for a USING list that mentions all columns in the two tables that have the same ...


5

I can't recommend polymorphic associations. In my experience, this will lead to data consistency issues since you can't use a foreign key constraint in the schema. That means you're reliant on your application's writes to this part of the database being bug-free, and no one manually altering the data. It can also make the queries which join Upvotes to >1 ...


5

Postgres can use indexes on the foreign server. But there are quite a few more obstacles than for local tables. Read the chapter Remote Query Optimization in the manual. Comments in the current source code of postgres_fdw.c are revealing, too: 521 * [...] For a foreign 522 * table, we don't know what indexes are present on the remote side but 523 * want ...


5

This question (in various forms) crops up regularly on this forum. It is called EAV (Entity Attribute Value). For good reason, it is called an antipattern. There are many reasons not to use this form of data model. They are outlined here, here and here (and links within + look at other posters' replies in the same threads). Not everyone agrees that EAV is ...


5

A Schema's owner is always a User (or Database Role). There may be a Schema with the same name as a User, because in very old versions of SQL Server a User and a Schema were the same thing. So there's still a User called dbo and a Schema called dbo in every database. A Schema's owner is a User who has full control of the schema, and who will be the ...


4

If this is something you are planning to do regularly (i.e. it is part of the application logic and not a one-off data transformation exercise) then you could use a view onto Table1 and Table2 with an INSTEAD OF INSERT trigger to manage splitting the data (and arranging the keys/relationships) - then you would just do: INSERT newView SELECT NEWID(), A, B, C,...


4

I think you are trying to blend together concepts from object modeling and concepts from data modeling in a way that isn't helping you to clarify your own understanding of the problem. I hope I can clear the clutter a little without too much rambling. The relational model, as such, does not support inheritance, never mind polymorphism. This means that a ...


4

There is nothing in your question that would speak against inheritance. One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually. ...


4

All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general. The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include: You have substantially fewer rows in your tables for the ...


4

There is nothing like this in SQL Server. What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no SQL specific templating for this in SSDT, you might be able to use T4 templates to do something similar. If that interested you then another alternative for ...


3

Seems like you want: INSERT dbo.Table1(A,B,C) SELECT A,B,C FROM dbo.DataTable WHERE <identify one row>; INSERT dbo.Table2(ID,D,E,F) SELECT SCOPE_IDENTITY(),D,E,F FROM dbo.DataTable WHERE <identify that same row>; Or maybe just use one table, if you're always going to have a row in each table... do you have a good reason for splitting ...


3

Whether you are better off with single-table-inheritance or class-table-inheritance really depends on the particulars of your case. The performance advantage can go either way. The difficulties presented by having lots of NULLS in a table range from the trivial to the overwhelming. It depends on what your data looks like, and what you intend to do. Kudos ...


3

Using one supertable Tag will give you less tables in your schema, but you will basically lose guarantees about referential integrity. If one column in another table must have one 'colour' value, you add a referential constraint to the table 'colours' (one of your dictionaries). If you have a big one, you can have one column that should have one colour to ...


2

Use always the smallest data types that fits the values it will hold. 'Fit' is a flexible term, depending on thing like: Can it hold all current values of the table? Can it hold future values of the table? Is the data in a normal form? Can I create duplicate values? (think string vs. integer id that is a foreign key to an external table) Can I insert ...


2

In theory and in the general case in practice for OLTP it would be bad practice to have the extra key: you are duplicating information that can be derived from elsewhere. You have the extra key value to store (and presumably index) and if a PeripheralUnit could move from one area to another then unless the area values on the relevant device records are ...


2

This looks like the party relationship model (see https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories). I recently went through something similar, combining different content types under a single table with subtables for each unique content type. I'm not sure about the entity framework, but in the new model you ...


2

In this scenario, as James Anderson suggests, one-to-one relationships are not the way to do it. One patient may be checked up, operated on, discharged etc., more than once. The tables you have now should be renamed as follows and given their own unique ID primary keys to better represent what they are trying to model: PatientRegular to Regular ...


2

If you have common attributes across non-exclusive tables, you should consider normalizing your tables. It appears that Client, Employee, Manager, and Developer are all sub-types of the Person entity. As such any attributes common to all types should be in the person entity, and attributes for the other entities should depend only on that sub-type. I ...


2

this is not possible out of the box. inheritance in PostgreSQL is based on individual tables, which are only logically connected through pg_inherit. Each table is still somewhat independent. Therefore you cannot create a key on the entire structure. It is only possible to reference individual tables. What you can do, however, is to write a trigger ensuring ...


2

Not an answer for postgres, but may be useful nevertheless to someone using Oracle who stumbles across this: Oracle allows partial uniqueness to be enforced across multiple tables using a fast refreshing materialised view. Tom Kyte describes it here. In short, if a join produces any rows on commit, it violates a constraint on the materialised view. ...


2

I want to call attention to one of your business rules: Representative is Customer and is Company. This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes. Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is ...


2

One method is to introduce a PersonType attribute. Using this as a composite key along with a check constraint and foreign key in the Student and Teacher tables will ensure a row for a given person exists in only one of those entities and in the proper table. I can't speak to how well this implementation will get along with EF. There are similar ...


2

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc. For DBMS:s that support queries in check constraints you can do something like: ALTER TABLE ...


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