Primary Key string + autoincremet ID

SoIntMan

Hallo Leute,

frage zum Datenbank Design (SQL). Ich habe eine Datenbank in der Elemente eindeutig mit StringID abgelegt sind:

create table devices(
  id, INT NOT NULL AUTO_INCREMENT,
  devices_id             VARCHAR(100) NOT NULL,
  ...
  PRIMARY KEY(devices_id)
);

dann habe ich eine Daten DB, welche Daten in 1-N (id=> devID) Beziehung zu devices haben..
nun will ich nicht dass in der DatenDB zu jedem Datensatz ein DeviceID string sondern die ID in Bezug steht.

create table data(
  devId INT,
  timestamp TIMESTAMP ,
  data ... 
  ...
);

kann ich da so machen, dass die StringID eindeutig bleiben durch den primary key, und dazu den Autoincrement . Oder kann man das anders machen?

Vielen Dank

DocShoe

Ja dann trag doch die device_id ein und stelle die Verknüpfung über [data.devId] zu [devices.id] her? Oder verstehe ich da grad grundsätzlich was nicht?
PS:
Groß-/und Kleinschreibung bei Namen (Tabellen, Felder, etc.) können gern Probleme machen, vllt bleibste besser bei Kleinschreibung.

PPS:
Deine "DatenDB" ist eine Tabelle, oder?

SoIntMan

@DocShoe sagte in Primary Key string + autoincremet ID:

PPS:
Deine "DatenDB" ist eine Tabelle, oder?

hi Doc,

ja das is eine tabelle.. womöglich muss ich aber je nach Menge irgendwann in mehrer Files partionieren.. das muss ich die querys dann selbst zusammen mergen .. nehme ich an

wob

@SoIntMan sagte in Primary Key string + autoincremet ID:

kann ich da so machen, dass die StringID eindeutig bleiben durch den primary key, und dazu den Autoincrement . Oder kann man das anders machen?

Wenn alles über IDs laufen soll, dann sollte doch auch die ID der Primärschlüssel sein.
Ich habe dich jetzt so verstanden, dass du den Primärschlüssel aktuell nur benutzt, um die devices_id eindeutig zu halten? Dann könntest du einfach einen unique Constraint auf die Spalte device_id setzen.

SoIntMan

@wob sagte in Primary Key string + autoincremet ID:

Ich habe dich jetzt so verstanden, dass du den Primärschlüssel aktuell nur benutzt, um die devices_id eindeutig zu halten? Dann könntest du einfach einen unique Constraint auf die Spalte device_id setzen.

ahhh.. ja gute idee.. ja du hast mich richtig verstanden.. dann so ungefähr?:

[CREATE TABLE devces
(  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  deviceId VARCHAR NOT NULL,
....
  CONSTRAINT deviceId UNIQUE (deviceId )
);

@SoIntMan sagte in Primary Key string + autoincremet ID:

nun will ich nicht dass in der DatenDB zu jedem Datensatz ein DeviceID string sondern die ID in Bezug steht

Warum?

DocShoe

@SoIntMan sagte in Primary Key string + autoincremet ID:

ja das is eine tabelle.. womöglich muss ich aber je nach Menge irgendwann in mehrer Files partionieren.. das muss ich die querys dann selbst zusammen mergen .. nehme ich an

Welches DBMS?

SoIntMan

@manni66 sagte in Primary Key string + autoincremet ID:

Warum?

wegen der performance!? und overhead? oder wird ein string hash erzeugt?

=> DBMS: SqlLite

SeppJ

@SoIntMan sagte in Primary Key string + autoincremet ID:

@manni66 sagte in Primary Key string + autoincremet ID:

Warum?

wegen der performance!? und overhead? oder wird ein string hash erzeugt?

Auch Strings können indiziert werden, was ausreichende Performance für alle Aktionen gibt. Wobei ints natürlich trotzdem etwas flotter sind, da in der Regel kleiner. Aber deine Integer-Id ist ja auch Overhead durch zusätzliche joins und zusätzliches Speichern eines Datums das eigentlich keine Bedeutung hat. Musst du halt abwägen (und besser noch: Testen!), was mehr wiegt. Hast du das getan? Wahrscheinlich nicht.

ABER: Brauchst du auch gar nicht. Denn die scheinbar bedeutungslose Integer-Id hat einen anderen entscheidenden Vorteil: Sie ist bedeutungslos. Warum ist das ein Vorteil? Weil 'Device-Id' klingt nach etwas, das in irgendeiner Form eine Bedeutung in der echten Welt hat, und solche Daten sind selten/nie gute Kandidaten für primary keys einer Datenbank. Denn die echte Welt ist weder statisch noch einzigartig, was aber die Anforderung an den PK ist. Derweil mag es so aussehen, dass eine 'Device-Id' unveränderlich für alle Zeiten ist, aber in 2 Jahren soll dann noch ein anderer Devicetyp in die Datenbank, dessen Ids sich mit den anderen überschneiden und man braucht noch einen Typidentifier um unique zu bleiben. Oder das Engineering sagt, dass alle DeviceIds geändert werden müssen und auf DeviceId.v.6 umgestellt werden muss. Oder, oder, oder. Mit einem bedeutungslosem Int als PK ist das alles kein Problem im Datenmodell, dann ist das bloß eine neue Spalte in der Zuordnung id->DeviceId. Hätte man die DeviceId als PK genommen, müsste man hingegen das gesamte Datenmodell ändern.

Von daher: Gute Idee, aber aus anderen Gründen als ihr wahrscheinlich denkt.

wob

Es darf auch die Frage gestellt werden, ob SQLite optimal ist. Denn wenn du schon oben davon sprichst, dass du partitionieren muss, dann ist womöglich eine "Nicht-Lite"-Datenbank besser geeignet. Ich weiß weder, wie komplex deine DB ist noch wie groß sie ist und was sonstige Anforderungen sind - von daher will ich aber NICHT generell sagen, dass SQLite ungeeignet ist.

SoIntMan

@wob sagte in Primary Key string + autoincremet ID:

Es darf auch die Frage gestellt werden, ob SQLite optimal ist. Denn wenn du schon oben davon sprichst, dass du partitionieren muss, dann ist womöglich eine "Nicht-Lite"-Datenbank besser geeignet. Ich weiß weder, wie komplex deine DB ist noch wie groß sie ist und was sonstige Anforderungen sind - von daher will ich aber NICHT generell sagen, dass SQLite ungeeignet ist.

Das is ein sehr gute Einwand. Momentan is noch nich alles so klar wie das design aussieht. Das Problem ist, dass das properitäte Software framework momentan nur SQLLite intus hat... Die idee ar auch große Danktenmengen (timeSeries) in ner InfluxDB o.ä. ab zulegen..

Ist momentan noch ne Sache des Aufwands .. muss abwägen .. Kosten PoC etc.

DocShoe

@SoIntMan
Ich denke auch, dass ein "echter" DB Server vielleicht die bessere Wahl ist. Dedizierte Server können Abfragen wahrscheinlich besser planen oder sogar von Haus aus parallelisieren, das müsstet ihr dann selbst implementieren. Natürlich nur, falls das notwendig wird. Wenn Daten nicht nur lokal benötigt werden sondern netzwerkweit musst man die SQLite Datei(en) mit R/W Berechtigungen freigeben, das schreit schon nach Problemen. Aber ich kenne eure Anforderungen nicht, vllt schreibst mal ein bisschen was dazu.

SoIntMan

@DocShoe sagte in Primary Key string + autoincremet ID:

@SoIntMan
Ich denke auch, dass ein "echter" DB Server vielleicht die bessere Wahl ist. Dedizierte Server können Abfragen wahrscheinlich besser planen oder sogar von Haus aus parallelisieren, das müsstet ihr dann selbst implementieren. Natürlich nur, falls das notwendig wird. Wenn Daten nicht nur lokal benötigt werden sondern netzwerkweit musst man die SQLite Datei(en) mit R/W Berechtigungen freigeben, das schreit schon nach Problemen. Aber ich kenne eure Anforderungen nicht, vllt schreibst mal ein bisschen was dazu.

guten Morgen Doc,

ja evtl. nehme ich ne hybrid Lösung.. SqList als "Config" Datenbank, alles Daten des System ausgenommen Long-term (Timreseries). diese würde ich dann via InfluxDB loggen.. und wenn ich ne query mache muss ich eben selbst was basteln.. Partitionierung etc.

Wie sähe es denn aus mit der concurrency wenn ich SQLite Datenbanken von mehren Teilnehmern verbinde.. bekomme ich da Schwierigkeiten bei Read/write Geschichten, oder synchronisiert das die SQLite db von sich aus.. hmmm

DocShoe

@SoIntMan
SQLite unterstützt verschiedene Concurrency Modi, die muss man aber explizit aktivieren (oder beim Übersetzen durch Compilerschalter setzen). Unter Windows benutzt SQLite named Mutexes, damit lässt sich SQLite nur lokal von mehreren Benutzern sicher ansprechen (nur Schreiben, Lesen geht immer parallel). Der Netzwerkzugriff von verschiedenen Rechnern auf die gleiche SQLite DB lässt sich nicht synchronisieren.
Hier ein Überlick.

hustbaer

@DocShoe sagte in Primary Key string + autoincremet ID:

Unter Windows benutzt SQLite named Mutexes, damit lässt sich SQLite nur lokal von mehreren Benutzern sicher ansprechen (nur Schreiben, Lesen geht immer parallel).

Bist du sicher? Ich kann nichts in der Richtung finden.
Hier: https://sqlite.org/lockingv3.html steht dass File-Locks verwendet werden. Das sollte auch über's Netzwerk funktionieren. Vorausgesetzt das File-Sharing Protokoll das man verwendet hat eine korrekt funktionierende Locking Implementierung.

Falls du das SQLITE_OPEN_FULLMUTEX Flag meinst: Damit wird der Zugriff auf ein SQLite Connection Objekt synchronisiert, nicht der Zugriff auf die Datenbank. (BTW: Thread-safe DB Connection Objekte auch eher unüblich, normalerweise macht man pro Thread eine eigene DB Connection.)

DocShoe

@hustbaer sagte in Primary Key string + autoincremet ID:

@DocShoe sagte in Primary Key string + autoincremet ID:

Unter Windows benutzt SQLite named Mutexes, damit lässt sich SQLite nur lokal von mehreren Benutzern sicher ansprechen (nur Schreiben, Lesen geht immer parallel).

Bist du sicher?

Nein, natürlich nicht
Hab mir das aus den Infos zu SQLite und ein paar eigenen Erfahrungen so zusammengereimt, gut möglich, dass ich da falsch liege.

SoIntMan

Guten Abend,

mal noch ein paar Fragen:

Kann man queries auch asynchron absetzen? Angenommen ich will eine querie abbrechen können, welche zu langen dauern würde etc. Was wäre da für ein DBMS nötig? geht das mit InfluxDB, SQLLite!?
wie ist es sinnvoll eine Datenbank zu partitionieren, wenn ich daten über einen langen zeitraum aufzeichen, und sie auch "relativ schnell" wieder abfragen möchte. Da machen wohl timeseries db am meisten sinn .. gell.. und wie partitionieren die intern?
habt ihr erfahrungen mit InfluxDB, QuestDB, TimescaleDB gemacht? sie sollte erstmal "kostenlos" sein;)

DocShoe

Ich habe nur Erfahrung mit PostgreSQL:

Timeout für queries lässt sich proprietär mit statement_timeout setzen. statement_timeout wird ab V9.6 benutzt, aber ich selbst habe das noch nie eingesetzt.
Ab V10 unterstützt PostgreSQL Declarative Table Partitioning, da gibt man für eine Tabelle an, in welchem Bereich sich ein Wert bewegt. Die einzelnen Partitionen mit ihren Constraints muss man allerdings selbst pflegen, d.h. man muss ggf. neue Tabellen für neue Intervalle erzeugen. Da scheint aber noch Optimierungspotenzial drinzustecken, denn bis V13 wird da immer noch dran gearbeitet um bessere Performance zu erzielen.
Wir haben mit Postgres V9 ein Tool, das Messdaten in einem 15 Sekunden Intervall aufzeichnet und daraus Diagramme bastelt. Jedes Diagramm besteht so aus 5-12 Kurven und es werden gleichzeitig bis zu vier Diagramme angezeigt. Das war mit Postgres bis V9 nur mit viel Trickserei hinzukriegen (keine vollen Updates der Diagramme, sondern nur inkrementell für den Anzeigebereich, der aktualisiert werden muss. Wenn sich der Anzeigebereich von 8:35:00 - 9:35:00 auf 8:35:15 - 9:35:15 ändert werden z.B. nur die Daten für 9:35:00 bis 9:35:15 angefordert und die bestehenden Daten aktualisiert. Außerdem sind in der db in den Messwert noch Statusinformationen reinkodiert worden, damit alle Daten in einer Tabellenspalte zur Verfügung stehen. Das Auslesen aus der Software ist kein Problem, aber per SQL in die Datenbank zu gucken, um sich die Messwerte anzusehen geht halt nicht. Damals hätte ich schon gerne Table Partitioning gehabt, aber das ging nur umständlich über Vererbung und da war auch nicht klar, ob das besser performt.

Wie du deine Tabellen partitionierst hängt von deiner Anforderung ab, aber wenn es Zeitverläufe sind scheint mit einer Partitionierung nach Zeitstempel schon sinnvoll. Wichtig (zumindest bei PostgreSQL) ist auch, dass man nicht zu viele Partitionen erzeugt, weil das Bestimmen der relevanten Partitionen auch Zeit kostet. Auf Stackoverflow habe ich mal gelesen, dass Partitionierung bei PostgreSQL erst bei Tabellen Sinn macht, die mehrere Millionen Einträge haben (ausm Gedächtnis, ohne Referenz). Zur Not musst du das halt mal selbst mit synthetischen Daten prüfen.

Wie gesagt, über Timescale DB bin ich iwann mal gestolpert, bin aber nie dazu gekommen, das mal auszuprobieren. Wenn du dazu iwann mal Ergebnisse hast würde ich mich sehr über Infos freuen.