2

I'm new to PostgreSQL and SQL in general and I've got a bit of an issue understanding PKs, FKs an serial id and when to use them. I'll try to add info on what I have already tried so far, but I can't recall all of it as I've done a lot of trial and error.

https://dbdiagram.io/d/biodeg-65f43429ae072629ce19c2b8

This is a reduced version of my tables. I'm populating the db parsing and formatting data from a csv file via python and sqlalchemy. Atm the db is supposed to be

Per export/import the tables get:

  1. ONE new record in the "Set Metadata"-table. (with "Set" being the unique value here)
  2. UP TO SIX new records in the "Measurement Meta Data"-table (all of which with the same "Set"-value as the record added in 1., only being unique as a combination of "Set" and either "Kopf", "ID" or "SN Nr."
  3. UP TO SIX TIMES 360 new records in the "Measurement Data"-table (Again, the combination of "Set" and "Kopf" (or "ID"/"SN Nr") is supposed to act as a way to match with a record from the "Measurement Meta Data"-table.

So my questions:

  1. Should I add auto-incrementing serial ids to these tables? Intuitively it makes sense for table 1 and 2, not as much for table 3 as 360 data points from a single test don't really need an incremented id?
  2. Let's assume I'm not adding any ids: One of my ideas setting PKs, FKs looked like this:

.

  • PK for "Set" in the "Set Metadata"-table.
  • Composite PK for "Set" and "Kopf" in the "Measurement Meta Data"-table
  • FK for "Set" in the "Measurement Meta Data"-table REFERENCING "Set" in the "Set Metadata"-table.
  • FK for "Set" + "Kopf" in the "Measurement Data"-table REFERENCING "Set" and "Kopf" in the "Measurement Metadata"-table.
  • also, I think I had to set some unique constraints in order to set these FKs.

However, this didn't work out. In PowerBI this turned out as a 1:1 relationship between Table 1 & 2, which should have been a one to many relation(?). Also there was no relationship at all between table 2 & 3.

So, which way should I use PK and FK for my 3 tables?

Also, if I'm recommended to add ids to these tables, let's say "set_id" for table 1 and "measurement_id" for table 2 and these become my PKs. Then I'd need "set_id" as a FK in table 2, but how would I add the "set_id" to the table with the same value as the referenced "set_id" from table 1. After all, I would need up to 6 records with the same "set_id".

Big thanks in advance and I hope this is not asking too much

2
  • "only being unique as a combination of "Set" and either "Kopf", "ID" or "SN Nr." " well which one? One of those three along with Set must be a unique surely? Sample data would help. Mar 27 at 14:31
  • It can be either one of those three in combination with "Set". "Kopf", "ID" and "SN Nr." are three different values with the same purpose, identifying the data logger the data comes from. So admittedly I could just store only one of these 3 columns without losing any informational value.
    – JUHJUH
    Mar 27 at 15:22

2 Answers 2

4

Each "main" Entity (or Table) represents instances (i.e. Records) of an object that can exist in their own right, in the absence of any other Entity, e.g. a Person. The attribute that uniquely identifies each instance is a candidate for the Primary Key on that table. The idea of a Primary Key is that you determine (or allocate) its value when a record is first created and that value remains unchanged throughout the entire lifetime of that record until it is finally and permanently destroyed.

For example, a Person can be uniquely identified by their Social Security Number but there might be very exceptional circumstances where even that has to be changed. In such cases, an arbitrary, numeric identifier will do as the Primary Key instead (but still with a unique constraint on the SSN). YMMV.

From your diagram, I'd say your Primary Keys and Foreign Keys should be:

"Set MetaData" 
   PK:(Set)

"Measurement MetaData" 
   PK:(Set, Kopf)
   FK:(Set) -> "Set MetaData" (Set)

"Measurement Data"
   PK:(Set, Kopf, Time)
   FK:(Set, Kopf) -> "Measurement MetaData" (Set, Kopf)

Now some people might throw their hands up in horror at these Composite, Natural, Primary Keys but, as long as you've chosen them correctly, they're not a problem.

In PowerBI this turned out as a 1:1 relationship between Table 1 & 2, which should have been a one to many relation(?). Also there was no relationship at all between table 2 & 3.

I would suggest that your toolset is letting you down, not your Data modelling.

1
  • Also, not every person does have a US social security number :-)
    – Bergi
    Mar 27 at 18:24
2

If you're new to sql:

(set, kopf, time) is a tuple.

Saying (set, kopf, time) is unique means there can be only one row with the specific values (set, kopf, time). It does not mean any of the columns is unique, only the specific combination of specific values in the columns is unique. It also means for each specific values of (set, kopf) then there can be only one row per value of (time).

  • Measurement table.

If (set, kopf, time) is unique, then it can be the primary key. Since a primary key automatically creates an index on (set, kopf, time), this also gives you a fast way to SELECT WHERE set=... AND kopf=... ORDER BY time.

If the data naturally gives you a primary key, you don't need to create an id.

In this case though, there's a bit of a doubt because "set" is varchar. If it is only a few characters then fine. However if it is a large string, and you have a lot of rows, you may find that a significant proportion of the disk space used by your table consists of this string. In this case it may be useful to add an id to table "Measurement meta data", remove set and kopf from "Measurement" and use that id instead.

Another situation when you may get into trouble is if there was a mistake when typing the string values used in "set" and "kopf", or any situation which requires updates. To update these values, if they are used as foreign keys, you then must update them everywhere. This can be done automatically via ON UPDATE CASCADE, but if there are many rows it can take a while. Also it will not update stuff outside of the database, like filenames of plots that were generated using the data, or legends on the plots that say "dataset number whatever".

The point of generated id's is there's nothing special about them, they don't mean anything besides referring to a particular row, so they never need to be updated and they are never reused. Thus, stuff outside the database that may refer to specific rows using the id will not break the reference if the title, name or other text fields are updated. I'm using plots as an example, but you get the idea, it can be anything. If you use something as primary key, it should never need to be updated.

Should I add auto-incrementing serial ids to these tables? Intuitively it makes sense for table 1 and 2

For set_metadata, I'd say yes.

For measurement_metadata, I'm not sure. You can either:

  • Use (set_id,kopf) as PK, then the same as FK in measurement_data

  • Add an autogenerated id PK to measurement_metadata which then identifies the row with specific (set_id,kopf), then use this id in measurement_data.

not as much for table 3 as 360 data points from a single test don't really need an incremented id?

yes: presumably you don't have two measurements at the same time for the same (set,kopf), so you don't need an extra id to distinguish them.

how would I add the "set_id" to the table with the same value as the referenced "set_id" from table 1. After all, I would need up to 6 records with the same "set_id".

Suppose "set_metadata" has set_id as PK. When your python code does the insert, it grabs the id that was inserted thanks to "INSERT ... RETURNING" clause. You can then use this value for the 6 rows to insert into "measurement_metadata".

The same thing applies to the other tables, every time you have a FK: insert into the parent table, grab the generated id, use it to insert into the child table.

Remember to wrap the whole thing into a transaction. This ensures it will either work or fail (no incomplete data can be inserted) and it is much faster as it avoids per-row COMMIT overhead.

1
  • For reasons quoted above in the answer, but it depends what kind of data OP calls "Set".
    – bobflux
    Mar 27 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.