Literaturangaben in einer DB

Hallo zusammen,

ich tauche gerade erst zunhemend in das Postgres/ Postgis Universum ein. Bislang klappt das meiste, jetzt währe ich für etwas Input sehr dankbar.

Ich habe eine Tabelle mit Orten (diverse Attribute und geogr. Koordinaten), das funktioniert soweit alles gut. Nun würde ich gerne die Möglichkeit schaffen, Literaturangaben hinzuzufügen.
Das soll Autor + Jahr (via Fremdschlüssel aus Literaturtabelle) und Seitenangabe sein.

Eine Literaturtabelle nach folgendem Prizip existiert bereits:
literatur:
| lit_nr (PK) | lit_kurzzitat | lit_zitat |
| 1 | Mustermann 2007 | Musterman, Lorem ipsum (2007) |

Die Orte DB schaut etwa so aus:
orte:
| orte_id | orte_lit_kurzzitat (FK) | orte_lit_text | orte_literatur|
| 1 | 1 | s. 3-5. | 1, s. 3-5. |

“orte_lit_kurzzitat” referenziert auf literatur (lit_nr).

Die Literaturangabe “orte_literatur” ist ein einfacher concat von “orte_lit_kurzzitat”

Ich hoffe die Idee ist klar: Fehlerhafte Schreibweisen bei den Autoren und Jahreszahl sollen mittels Fremdschlüssel verhindert werden, die Seitenzahlen variieren natürlich. Ich bin mit der Lösung allerdings nicht zufrieden, bestimmt geht es besser.

Das Ganze funktioniert (wie es jetzt ist) zudem nicht, wenn es mehrere Literaturangaben gibt. Eine extra Tabelle anzulegen, in der jedes Literaturzitat mit Seitenzahl abgelegt wird und über FK angesprochen wird würde ich ungern machen.

Schön wäre es zudem, wenn in der orte (orte_literatur) statt der Codierung 1 das lit_kurzzitat auftauchen würde, also: Musterman 2007, s. 3-5.

Habt ihr eine Idee?

LG

Vorerst habe ich das nun mit einer zusätzlichen Tabelle gelöst, in der jedes Kurzzitat eine eigene Spalte hat + eine eigene Spalte für die Seitenangabe. Keine schöne Lösung, ich hoffe ihr habt noch Vorschläge/ weiterführende Links.

Die einzelnen Spalten möchte ich nun zusammenführen in eine Ergebnisspalte.
So funktioniert es:
UPDATE literatur_eingabe SET
literaturzitat_gesamt = CONCAT (kurzzitat_1::integer, ‘,’ ,kurzzitat_1_text::text);

kurzzitat_1 ist allerdings ein FK, hier sollte der Wert der “parent table”-Spalte mit den Kurzzitaten erscheinen, nicht die ID.

Sowohl ein JOIN
UPDATE literatur_eingabe SET
literaturzitat_gesamt = CONCAT ((SELECT lit_kurzzitat FROM literatur RIGHT JOIN literatur_eingabe ON literatur.lit_nr = literatur_eingabe.kurzzitat_1)::text, ‘,’ ,kurzzitat_1_text::text);

als auch WHERE

UPDATE literatur_eingabe SET literaturzitat_gesamt = CONCAT
(SELECT lit_kurzzitat AS lit FROM literatur, literatur_eingabe WHERE lit_nr = literatur_eingabe.kurzzitat_1);

funktionieren hier wegen “Unterabfrage ergab mehrere Zeilen” nicht.

Wie kann ich das lösen?

du such sowas wie :

test=# create table artikel (id int primary key, name text);
CREATE TABLE
test=*# insert into artikel values (1, 'artikel 1');
INSERT 0 1
test=*# insert into artikel values (2, 'artikel 2');
INSERT 0 1

test=*# create table bestellung(id int primary key, artikel int references artikel, wer text);
CREATE TABLE
test=*# insert into bestellung values (1, 1, 'meier');
INSERT 0 1
test=*# insert into bestellung values (2, 1, 'schulze');
INSERT 0 1
test=*# insert into bestellung values (3, 2, 'schubert');
INSERT 0 1

test=*# alter table bestellung add column t text;
ALTER TABLE

test=*# select b.id, b.wer || ' kaufte ' || a.name from bestellung b left join artikel a on b.artikel=a.id;
 id |         ?column?          
----+---------------------------
  1 | meier kaufte artikel 1
  2 | schulze kaufte artikel 1
  3 | schubert kaufte artikel 2
(3 Zeilen)

test=*# update bestellung set t = foo.t from (select b.id, b.wer || ' kaufte ' || a.name as t from bestellung b left join artikel a on b.artikel=a.id) foo where bestellung.id=foo.id;
UPDATE 3
test=*# select * from bestellung ;
 id | artikel |   wer    |             t             
----+---------+----------+---------------------------
  1 |       1 | meier    | meier kaufte artikel 1
  2 |       1 | schulze  | schulze kaufte artikel 1
  3 |       2 | schubert | schubert kaufte artikel 2
(3 Zeilen)

test=*#

Wie Du siehst, kannst Du das aber auch im Select machen, Deine extra Spalte literaturzitat_gesamt ist redundant, sowas sollte man vermeiden.

Das klappt ganz wunderbar, herzlichen Dank!