Hvordan unngå flere verdier i en celle?

[Emancipate]

Jeg er helt ny på databaser. Et problem som alltid stopper meg er hva jeg skal gjøre når det er "naturlig" å putte flere verdier i en celle. Her er et eksempel:

 

Jeg skal lage en database som inneholder bøker (i én tabell) og tilhørende forfattere (i en annen tabell).

- Hver bok må ha en ID og en tittel (dette er enkelt nok)

- Hver forfatter må ha en ID og et navn (dette er enkelt nok)

- Hver bok må ha "linker" til forfatteren (altså ID-en). Her ligger problemet idet de fleste bøker har en eller to forfattere, mens noen (novellesamlinger) kan ha 30 eller mer.

 

- Man må kunne søke på bøker vha forfatternavnet i fritekst.

- Man må også kunne liste opp alle bøker fra en forfatter. Ta med i betraktningen her at to forfattere kan ha samme navn, det skal gå an å liste opp alle bøkene fra kun én av dem (så lenge man har ID-en).

 

I en standard database-tutorial for nybegynnere vil man rote det til med å lage kun ett felt for forfatternavn. Det bryter begge forutsetningene "kan være mer enn en forfatter" og "to forfattere kan ha samme navn".

 

Hvordan kan man løse dette på en effektiv måte (tenk en million bøker med et par spørringer i sekundet)?

[Jonas]

En i vanlig relasjonsdatabase vil man typisk løse dette ved hjelp av en ekstra hjelpetabell. Denne har ingen semantisk mening, annet enn at den brukes for å lage en n-til-n relasjon mellom bøker og forfattere. Se for deg følgende tabeller. (Går forresten ut ifra at du bruker MySQL.)

 

CREATE TABLE `test`.`books` (
 `id` INT  NOT NULL AUTO_INCREMENT,
 `name` TEXT  NOT NULL,
 PRIMARY KEY (`id`)
);

CREATE TABLE `test`.`authors` (
 `id` INT  NOT NULL AUTO_INCREMENT,
 `name` TEXT  NOT NULL,
 PRIMARY KEY (`id`)
);

CREATE TABLE `test`.`authors_books_xref` (
 `id` INT  NOT NULL AUTO_INCREMENT,
 `book_id` INT  NOT NULL,
 `author_id` INT  NOT NULL,
 PRIMARY KEY (`id`)
);

Her er et par eksempler på spørringer som benytter denne strukturen.

 

Hente ut forfattere med tilhørende bøker

 

SELECT
   authors.name,
   GROUP_CONCAT(books.name SEPARATOR ', ') AS books
FROM
   authors
       LEFT JOIN authors_books_xref ON (authors_books_xref.author_id = authors.id)
       LEFT JOIN books ON (authors_books_xref.book_id = books.id)
GROUP BY
   authors.id

+----------------+------------------------------------------------+
| name           | books                                          |
+----------------+------------------------------------------------+
| Ola Nordmann   | Harry Potter 1, Harry Potter 2, Harry Potter 4 |
| Kari Nordmann  | Harry Potter 3, Harry Potter 5, Harry Potter 4 |
+----------------+------------------------------------------------+

Hente ut bøker med tilhørende forfattere

 

SELECT
   books.name,
   GROUP_CONCAT(authors.name SEPARATOR ', ') AS authors
FROM
   books
       LEFT JOIN authors_books_xref ON (authors_books_xref.book_id = books.id)
       LEFT JOIN authors ON (authors_books_xref.author_id = authors.id)
GROUP BY
   books.id

+----------------+--------------------------------+
| name           | authors                        |
+----------------+--------------------------------+
| Harry Potter 1 | Ola Nordmann                   |
| Harry Potter 2 | Ola Nordmann                   |
| Harry Potter 3 | Kari Nordmann                  |
| Harry Potter 4 | Ola Nordmann, Kari Nordmann    |
| Harry Potter 5 | Kari Nordmann                  |
+----------------+--------------------------------+

Endret 14. februar 2011 av Jonas

[Emancipate]

Takk, det var akkurat det jeg trengte å vite.