[PostgreSQL] problem z zaprojektowaniem bazy

Witam!
Mam problem...
muszę zrobić pewien projekt, a moja wiedza na temat baz danych jest... no cóż... żadna, ogranicza się tylko do kilku komend.
Baza ma zawierać różnego typu publikacje (artykuły, książki, materiały konferencyjne, prace magisterskie, doktorskie itp. w sumie 12 rodzajów publikacji) i nie bardzo wiem jak się do tego zabrać, niby sprawa prosta: publikacja może mieć wielu autorów, a autor może napisać kilka publikaji czyli relacja wiele do wielu, jednak dla takiego laika jak ja nie jest to takie proste.
Stworzyłem schemat, skrypt tworzący bazę, jednak nie jest to zrobione tak jak powinno. Prosiłbym, aby ktoś fachowym okiem rzucił na ten skrypt i podpowiedział mi ewentualnie jakie zmiany mam wprowadzić, aby było dobrze.
Skrypt zamieszczam poniżej.
Dodam jeszcze, że bazę muszę stworzyć w PosrgreSQL (aktualnie posiadam werjse 8.3 i pracuje na Viście).

create table author  (id int NOT NULL ,
            imię VARCHAR(30) NOT NULL,
            nazwisko VARCHAR(40) NOT NULL,
            PRIMARY KEY (id));
create table article (id int NOT NULL,
            author_id int NOT NULL,                             
            title varchar(200) NOT NULL,               
            jurnal varchar(100) NOT NULL,
            year int NOT NULL,                
            volume int,               
            number int,
            PRIMARY KEY  (id));
create table author_to_article (author_id int NOT NULL,
            article_id int NOT NULL);

create table book (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,
                publisher varchar(200) NOT NULL,  
                year int NOT NULL,                 
            volume int,               
            series varchar(30),              
            adress varchar(50),              
            editor varchar(50),              
            month varchar (12),              
            note varchar (50),
            PRIMARY KEY (id));
create table author_to_book (author_id int NOT NULL,
            book_id int NOT NULL);

create table inproceedings (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,               
            booktitle varchar(200) NOT NULL,             
            year int NOT NULL,                
            volume int,               
            series varchar(30),              
            adress varchar(50),              
            pages int,              
            publisher varchar(200),
                organization varchar(50),
                chapter varchar(50),              
            editor varchar(50),
                month varchar (12),
             note varchar (50),
            PRIMARY KEY (id));
create table author_to_inproceedings (author_id int NOT NULL,
            inproceedings_id int NOT NULL);

create table booklet (id int NOT NULL,              
            title varchar(200) NOT NULL,               
            howpublished varchar(200) NOT NULL,             
            adress varchar(50),              
            year int,
                      month varchar (12),              
            note varchar (50),
            PRIMARY KEY (id));

create table inbook (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,              
            chapter varchar(50) NOT NULL,               
            publisher varchar(200) NOT NULL,
            year int NOT NULL,
            volume int,               
            series varchar(30),              
            adress varchar(50),              
            type varchar(50),              
            edition int,              
            month varchar (12),              
            note varchar (50),
            PRIMARY KEY (id));
create table author_to_inbook (author_id int NOT NULL,
            inbook_id int NOT NULL);

create table incollection (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,
            booktitle varchar(200) NOT NULL,            
            publisher varchar(200) NOT NULL,             
            year int NOT NULL,              
            editor varchar(50),
            volume int,              
            series int,
            type varchar(50),              
            chapter varchar(50),              
            pages int,              
            adress varchar(50),              
            edition int,              
            month varchar (12),               
            note varchar(50),
            PRIMARY KEY (id));
create table author_to_incollection (author_id int NOT NULL,
            incollection_id int NOT NULL);

create table manual (id int NOT NULL,
            author_id int NOT NULL,                
            title varchar(200) NOT NULL,                    
            organization varchar(50),              
            adress varchar(50),
            edition int,              
            month varchar (12),              
            year int,              
            note varchar(50),
            PRIMARY KEY (id));
create table author_to_manual (author_id int NOT NULL,
            manual_id int NOT NULL);

create table masterhesis (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,              
            school varchar(50) NOT NULL,              
            year int NOT NULL,              
            type varchar(50),
            adress varchar(50),              
            month varchar(12),              
            note varchar(50),
            PRIMARY KEY (id));
create table author_to_masterhesis (author_id int NOT NULL,
            masterhesis_id int NOT NULL);

create table phdthesis (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,              
            school varchar(50) NOT NULL,              
            year int NOT NULL,              
            type varchar(50),              
            adress varchar(50),              
            month varchar(12),              
            note varchar(50),
            PRIMARY KEY (id));
create table author_to_phdthesis (author_id int NOT NULL,
            phdthesis_id int NOT NULL);

create table techreport (id int NOT NULL,
            author_id int NOT NULL,
            title varchar(200) NOT NULL,              
            institution varchar(50) NOT NULL,
            year int NOT NULL,              
            editor varchar(50),              
            volume int,               
            series varchar(30),              
            adress varchar(50),
            publisher varchar(200),              
            organization varchar(50),              
            month varchar (12),              
            note char (50),
            PRIMARY KEY (id));
create table author_to_techreport (author_id int NOT NULL,
            techreport_id int NOT NULL);

create table misc (id int NOT NULL,
            author_id int NOT NULL,            
            title varchar(200),              
            howpublished varchar(200),              
            month varchar(12),              
            year int,              
            note varchar(50),
            PRIMARY KEY (id));
create table author_to_misc (author_id int NOT NULL,
            misc_id int NOT NULL);

Po co są te tabele author_to_... ?

Jeżeli w tabeli article jest author_id to już wystarczy...

Zamiast zakładać tabelę do każdego rodzaju publikacji zrób jedną tabelę publikacje i wsadź tam wszystkie niepowtarzające się pola z innych tabel, dodaj pole rodzaj_publikacji oraz załóż tabelę z rodzajami publikacji. Aha no i przydałoby się założyć klucze obce...

Idąc za Twoją radą zmieniłem cały schemat bazy jednak nie wiem jak połączyć teraz te tabele, chodzi mi o klucze główne i obce, mógłbyś mi powiedzieć jak to zrobić?

create table publication (publication_id int NOT NULL,
			                       the_kind_of_the_publication int NOT NULL,
			                       author_id int NOT NULL,
			                       title varchar(200) NOT NULL,
			                       booktitle varchar(200) NOT NULL,
			                       jurnal varchar(100) NOT NULL,
			                       year int NOT NULL,
			                       publisher varchar(200),
			                       volume int, 
		                               number int,
			                       series varchar(30),
			                       adress varchar(50),		      
			                       editor varchar(50),
			                       edition int,		      
			                       month varchar (12),	
			                       pages int,
			                       organization varchar(50),
		                               chapter varchar(50),
			                       howpublished varchar(200) NOT NULL,
			                       note varchar (50),
			                       school varchar(50),
			                       institution varchar(50));
create table author (author_id int NOT NULL,
			     first_name varchar(20),
			     last_name varchar(25));
create table the_kind_of_the_publication (the_kind_of_the_publication_id int NOT NULL,
			                                     the_kind_of_the_publication varchar(20));

create table publication
(
  publication_id int NOT NULL,
  the_kind_of_the_publication int NOT NULL,
  author_id int NOT NULL,
  title varchar(200) NOT NULL,
  booktitle varchar(200) NOT NULL,
  jurnal varchar(100) NOT NULL,
  year int NOT NULL,
  publisher varchar(200),
  volume int,
  number int,
  series varchar(30),
  adress varchar(50),                     
  editor varchar(50),
  edition int,                     
  month varchar (12),       
  pages int,
  organization varchar(50),
  chapter varchar(50),
  howpublished varchar(200) NOT NULL,
  note varchar (50),
  school varchar(50),
  institution varchar(50),
  PRIMARY KEY (publication_id),
  FOREIGN KEY (kind_of_the_publication_id) REFERENCES (the_kind_of_the_publication),
  FOREIGN KEY (author_id) REFERENCES (author)
);

create table author 
(
  author_id int NOT NULL,
  first_name varchar(20),
  last_name varchar(25),
  PRIMARY KEY (author_id)
);

create table the_kind_of_the_publication 
(
  the_kind_of_the_publication_id int NOT NULL,
  the_kind_of_the_publication varchar(20),
  PRIMARY KEY (the_kind_of_the_publication_id)
);

Poza tym zastanów się czy nie założyć indeksów (przyspieszą wyszukiwanie po tym polu) na najistotniejszych polach, np. title (booktitle i journal chyba można się pozbyć, nazwy można przechowywać w title).

W przypadku title indeks będzie wyglądał w ten sposób:
CREATE INDEX title_idx ON publications (title);

Analogicznie możesz zrobić w tabeli autorów wyszukiwanie po nazwisku.

Poza tym zastanów się czy nie założyć tabelki na szkoły, instytuty, organizacje (można te pola chyba złączyć ?!) ponieważ jeżeli będziesz miał wiele tytułów to szkoła/instytut/organizacja będą się powtarzać. To samo z wydawnictwami.

po pierwsze nazwy tabel/pól to nie są referaty ani pełne zdania opisujące ich funkcjonalność!
Zamiast the_kind_of_the_publication wystarczy publication_kind a zamiast the_kind_of_the_publication_id kind_id. Na miejscu tego, kto później miałby pisać zapytania do takiej bazy najpierw bym cię zje*** a później pier*** tym wszystkim i poszedł w ch*** :)

1. author_id wylatuje z publication
2. czym się różni title od booktitle?
3. co będzie w polach
   number
   adress
   chapter
   howpublished
4. czy pola school i institution będą wykorzystywane w tym samym rekordzie?
5. brakuje Ci tabeli autor_publikacja z polami
   autor_id,
   publikacja_id
   która pozwoli wyeliminować złączenie m do n

Misiekd ma rację, tj. author_id należy wywalić z publications i stworzyć tabelę dodatkową autor_publikacja. Autorów przecież może być wielu ;)

Zgodnie z sugestiami przerobiłem bazę:

create table publication (publication_id int NOT NULL PRIMARY KEY,
			the_kind_of_the_publication varchar(20) NOT NULL,
			title varchar(200) NOT NULL,
			booktitle varchar(200),
			jurnal varchar(100),
			year int NOT NULL,
			publisher varchar(200),
			volume int, 		      
			number int,
			series varchar(30),		      
			adress varchar(50),		      
			editor varchar(50),
			edition int,		      
			month varchar (12),		      
			pages int,
			organization varchar(50),
		        chapter varchar(50),
			howpublished varchar(200) NOT NULL,
			note varchar (50),
			school varchar(50),
			institution varchar(50),
  			FOREIGN KEY (kind_id) REFERENCES (publication_kind),
  			FOREIGN KEY (author_id) REFERENCES (author));
create index title_idx ON publications (title);

create table author (author_id int PRIMARY KEY NOT NULL,
			first_name varchar(20),
			last_name varchar(25));
			

create table author_to_publication (author_id int NOT NULL,
			publication_id int NOT NULL);

create table the_kind_of_the_publication (kind_id int NOT NULL PRIMARY KEY,
			publication_kind varchar(20));

z tym, że przy "FOREIGN KEY (kind_id) REFERENCES (publication_kind)" wywala mi:

 syntax error at or near "("
     FOREIGN KEY (kind_id) REFERENCES (publication_kind);

co do pytań Miśkad to:

1. booktitle to tytuł publikacji, którą nazywa się "materiałami konferencyjnymi" wszystko bierze się z BibTex'a, z którego otrzymałem wytyczne,
   2)number-nr wydania, adress-adres uczelni bądź wydawnictwa, chapter-rozdział, howpublished-rodzaj wydania, wydawnictwa. Wszystko to bierze się z BibTex'a, tam dla każdego rodzaju publikacji są określone pola wymagane i opcjonalne.
   3)pola school i institiution są osobnymi rekordami

Czy baza może tak wyglądać?

Przenieś ten:
FOREIGN KEY (kind_id) REFERENCES (publication_kind);
do tabeli author_to_publication

Skróć też nazwę tabeli the_kind_of_the_publication jest zdecydowanie za długa.

zrobiłem tak:

create table publication (publication_id int NOT NULL PRIMARY KEY,
			kind_of_publication varchar(20) NOT NULL,
			title varchar(200) NOT NULL,
			booktitle varchar(200),
			jurnal varchar(100),
			year int NOT NULL,
			publisher varchar(200),
			volume int, 		      
			number int,
			series varchar(30),		      
			adress varchar(50),		      
			editor varchar(50),
			edition int,		      
			month varchar (12),		      
			pages int,
			organization varchar(50),
		        chapter varchar(50),
			howpublished varchar(200) NOT NULL,
			note varchar (50),
			school varchar(50),
			institution varchar(50),
  	                FOREIGN KEY (author_id) REFERENCES author(author_id)
                        );
create index title_idx ON publication (title);

create table author (author_id int PRIMARY KEY NOT NULL,
			first_name varchar(20),
			last_name varchar(25)
			);
			
create table author_to_publication (author_id int NOT NULL,
			publication_id int NOT NULL,
			FOREIGN KEY (kind_id) REFERENCES publication_kind(kind_id)
                        );

create table kind_of_publication (kind_id int NOT NULL PRIMARY KEY,
			publication_kind varchar(20)
                        );

jednak przy tworzeniu tabeli publication krzyczy:

relation "author" does not exist

a przy tworzeniu tabeli author_to_publication:

relation "publication_kind" does not exist

jak bym tego nie zmienił zawsze wywala błędy przy kluczach obcych [???]

Czeski błąd... źle przekopiowałem

FOREIGN KEY (kind_id) REFERENCES publication_kind(kind_id) przenieś z powrotem do publications
a
FOREIGN KEY (author_id) REFERENCES author(author_id) przenieś do author_to_publication i będzie git

przeniosłem tak jak zaproponowałeś, ale przy tworzeniu tabeli publication z kluczem obcym krzyczy:

relation "publication_kind" does not exist

Zmień nazwę na taką jaką nosi tabela z rodzajami publikacji czyli kind_of_publication

FOREIGN KEY (kind_id) REFERENCES kind_of_publication(kind_id)

niestety :-(
dalej wywala błędy przy tworzeniu tabeli publication

column "kind_id" referenced in foreign key constraint does not exist</code>

lub też

relation "kind_of_publication" does not exist

Po pierwsze to przeczytaj co to jest klucz obcy i naucz się jak go zadeklarować w PostgreSQL. Gdybyś poświęcił na to chociaż 20 minut czasu to nie miałbyś tych banalnych i idiotycznych problemów.

Pierwszy komunikat mówi o tym, że w tabeli w której deklarujesz klucz nie ma pola kind_id. Jak się w tabeli publications nazywa pole z rodzajem publikacji? Zmień kind_id na tą nazwę.

Drugi komunikat mówi o tym, że nie ma takiej tabeli jak kind_of_publication zmień tą nazwę na taką jaką nosi tabela z rodzajami publikacji.

może i moje problemy są banalne i idiotyczne, ale wydaje mi się, że tylko dla kogoś kto się na bazach zna.
Ja się nie znam i dlatego zadaje takie pytania. Na temat kluczy trochę poczytałem i tym bardziej jestem zły, bo mi nie wychodzi. Wszystkie Twoje wskazówki skrupulatnie wykorzystuje do zmiany bazy jednak efektów nie ma. Poniżej umieszczam skrypt i proszę abyś rzucił okiem, bo ja na prawdę już nie wiem co tam jest nie tak i czemu to nie działa.

create table publication (publication_id int NOT NULL ,
			kind_of_publicationn varchar(20) NOT NULL PRIMARY KEY,
			title varchar(200) NOT NULL,
			booktitle varchar(200),
			jurnal varchar(100),
			year int NOT NULL,
			publisher varchar(200),
			volume int, 		      
			number int,
			series varchar(30),		      
			adress varchar(50),		      
			editor varchar(50),
			edition int,		      
			month varchar (12),		      
			pages int,
			organization varchar(50),
		        chapter varchar(50),
			howpublished varchar(200) NOT NULL,
			note varchar (50),
			school varchar(50),
			institution varchar(50),
			FOREIGN KEY (kind_of_publicationn) REFERENCES kind_of_publication(kind_id)
			);
create index title_idx ON publication (title);


create table author (author_id int PRIMARY KEY NOT NULL,
			first_name varchar(20),
			last_name varchar(25)
			);
			

create table author_to_publication (author_id int NOT NULL,
			publication_id int NOT NULL,
			FOREIGN KEY (author_id) REFERENCES author(author_id)
			);

create table kind_of_publication (kind_id int NOT NULL PRIMARY KEY,
			publication_kind varchar(20));

create table kind_of_publication 
(
  kind_id int NOT NULL PRIMARY KEY,
  publication_kind varchar(20)
);

create table publication 
(
  publication_id int NOT NULL PRIMARY KEY,
  kind_id int NOT NULL,
  title varchar(200) NOT NULL,
  booktitle varchar(200),
  jurnal varchar(100),
  year int NOT NULL,
  publisher varchar(200),
  volume int,                       
  number int,
  series varchar(30),                     
  adress varchar(50),                     
  editor varchar(50),
  edition int,                     
  month varchar (12),                     
  pages int,
  organization varchar(50),
  chapter varchar(50),
  howpublished varchar(200) NOT NULL,
  note varchar (50),
  school varchar(50),
  institution varchar(50),
  FOREIGN KEY (kind_id) REFERENCES kind_of_publication(kind_id)
);
create index publication_title_idx ON publication (title);


create table author 
(
  author_id int PRIMARY KEY NOT NULL,
  first_name varchar(20),
  last_name varchar(25)
);
                       

create table author_to_publication 
(
  author_id int NOT NULL,
  publication_id int NOT NULL,
  FOREIGN KEY (author_id) REFERENCES author(author_id)
);

1. musisz stworzyć tabelę z rodzajami publikacji przed stworzeniem klucza obcego do tej tabeli. Z tego też względu przeniosłem deklarację tej tabeli przed deklarację tabeli publication.
2. zmieniłem nazwę pola i typ z rodzajem publikacji w tabeli publication. Teraz będzie tam cyferka, która nic nie mówi ale po to są joiny aby powiązać i odczytać rodzaj publikacji z tabeli kind_of_publication.

Aha gdybyś chciał usuwać te tabelki to przez klucze zmieni się kolejność usuwania tabel. W pierwszej kolejności usuwaj tabele z kluczami. Prawidłowa kolejność usuwania:

drop table author_to_publication;
drop table publication;
drop table kind_of_publication;
drop table author;

To samo w przypadku usuwania rekordów. Nie będziesz mógł usunąć rekordu z kind_of_publication jeżeli w tabeli publication będziesz miał użyty ten rodzaj publikacji. Nie będziesz mógł usunąć autora z tabeli author jeżeli będzie użyty w tabeli author_to_publication.

super, bardzo mi pomogłeś, dzięki wielkie!