WN - Databasstruktur?

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - Databasstruktur? (https://www.wn.se/forum/showthread.php?t=4393)

Tvartom

2004-11-09 18:35

Hej!
Jag bygger just nu ett register med poster som ska vara indelade i kategorier.
Var post ska vara kunna tillhöra en eller flera kategorier.

De olika kategorierna ska kunna ändras, läggas till eller tas bort,
och ligga i en egen tabell.

Jag funderar därför hur man snyggast gör dessa realationer!

Detta är ett sätt, men jag gillar inte det riktigt:

Kod:

kategori-tabell:

id          beskrivning

1           "kat 1"

2           "kat 2"

3           "kat 3"





register-tabell:

id          post          kat

1           "reg1"         "1,2,"

2           "reg2"         "2,"

3           "reg3"         "1,2,3,"

Sökfråga för att söka kategori blir då i stil med:

Kod:

SELECT * FROM register-tabell WHERE kat LIKE '%".$katid.",%' ORDER BY ...

Att ta bort en kategeri får då göras med:

Kod:

DELETE FROM kategori-tabell WHERE id=".$katid." LIMIT 1;

UPDATE register-tabell SET kat = REPLACE(kat, '".$katid.",', '') WHERE 1

Att tilldelea en register post en registerpost får göras med:

Kod:

UPDATE register-tabell SET kat = CONCAT(kat, '".$katid.",') WHERE id = ...

Koden som den ser ut här fungerar ju dock inte, för finns en post med id 11 kommer en sökning på 1 hitta den. Detta går visserligen att komma runt med att fixa till koden lite, men det är lite på grund av detta som jag inte gillar strukturen.

Har någon förslag på ett bättre sätt?
/ Lasse

grazzy

2004-11-09 19:28

Du bör ha en post->kategori-tabell som ser ut nåt i stil med

post_id kategori_id

Det gör att du slipper spara relationerna som strängar.

mypay

2004-11-09 19:54

jag har i helgen satt upp ett system för en webbutik

där har jag

- produkter - här lagrar jag alla produkterna - utan någon info
[id] [produktnummer] [produktnamn] [kategori]

- produktattribut - här lagrar jag en lista på alla olika attribut.. storlek, pris, längd, mängd, vikt, tillverkare - whatever
[id] [namn]

- attribut - här lagras sen en hel jättelista med produkter (från -produkter-) och attribut (-produktattribut) och sen vilket värde dom har
[id] [produktID] [attributID] [värde]

nu kan jag alltså väldigt lätt skapa nya attribut för mina produkter utan att designa om databasen

Tvartom

2004-11-09 20:10

Tack för två snabba bra svar!

Citat:

Originally posted by mypay@Nov 9 2004, 20:54
- attribut - här lagras sen en hel jättelista med produkter (från -produkter-) och attribut (-produktattribut) och sen vilket värde dom har
[id] [produktID] [attributID] [värde]

nu kan jag alltså väldigt lätt skapa nya attribut för mina produkter utan att designa om databasen

En fråga: Hur gör du med datatypen på [värde]?

Robert

2004-11-09 20:22

Kod:

kategori-tabell:

id * * * * * * * * * *beskrivning

1 * * * * * * * * * * *"kat 1"

2 * * * * * * * * * * *"kat 2"

3 * * * * * * * * * * *"kat 3"



kategori-koppling-tabell:

id * * * * register-id * * * *kategori-id

1 * * * * * 1 * * * * * * * * * * *1

2 * * * * * 1 * * * * * * * * * * *2

3 * * * * * 2 * * * * * * * * * * *2

4 * * * * * 3 * * * * * * * * * * *1

5 * * * * * 3 * * * * * * * * * * *2

6 * * * * * 3 * * * * * * * * * * *3



register-tabell:

id * * * * * * * * * *post * * * * * * * * * 

1 * * * * * * * * * * *"reg1" 

2 * * * * * * * * * * *"reg2"

3 * * * * * * * * * * *"reg3"

Tvartom

2004-11-09 20:49

Jag kör på:

Kod:

kategori-koppling-tabell:

id * * * * register-id * * * *kategori-id

Nästa fråga blir då hur man skriver en SQL-fråga för att lista
alla registerposter och vilka kategorier de har?
Måste man köra en sub-fråga till varje register post?

Typ enligt denna principskissen:

Kod:

SELECT * FROM register-tabell;



Loop (register-tabell) {

 *SELECT kategori_id FROM kategori-koppling-tabell WHERE register-id = ".[register-tabell.id]

 *// Output info...

}

Eller kan man göra det med en JOIN-funktion i första SQL-frågan?
Alltså inte:

Kod:

SELECT * FROM register-tabell LEFT JOIN kategori-koppling-tabell ON register-tabell.id = kategori-koppling-tabell.register-id

för det ger mer än en rad per registerpost...

Susanne

2004-11-09 21:32

Nej. Du bör nog läsa på lite om SQL, bara ett tips. ;)

Om jag fattat vad du är ute efter rätt bör det bli så här:

Kod:

SELECT kategori, register FROM kategori-tabell, register-tabell, kategori-koppling-register-tabell WHERE kategori_id = kategori.id AND register_id = register.id

Tvartom

2004-11-09 22:09

Citat:

Ursprungligen postat av susja

Nej. Du bör nog läsa på lite om SQL, bara ett tips. *;)

Tja... då får även du ta fram boken... ;)
(Principskissen var principskiss för skripet jag kör (PHP i mitt fall) med frågor till SQL och inte ren SQL om det misstolkades... :unsure: )

Citat:

Ursprungligen postat av susja

Kod:

SELECT kategori, register FROM kategori-tabell, register-tabell, kategori-koppling-register-tabell WHERE kategori_id = kategori.id AND register_id = register.id

Hjälper inte mig med problemmet, tvärtom "LEFT JOIN" måste anvädas för att få fram de poster som helt saknar kategori!
(Det är inte heller kategori-namn-hämtning som behövs, utan kategori.id räcker!)

Det jag vill göra är att lista alla registerposter, och där även skriva ut vilka kategorier de tillhör.
Både min princip-skiss med att köra en extra SQL-fråga för var post och att köra med "LEFT JOIN" funkar!

Det blir bara så mycket frågor fram och tillbaks med första lösningen.

Andra lösningen skickar var registerpost lika många ggr som antalet kategorier den är med i. (Dock minst en!)
Naturligvis onödig data, samt det måste detekteras i skriptet hur många rader som var post har!

Det jag vill ha är en JOIN-funktion som bygger på med fler kolumner om det finns flera kategorier, istället för med nya rader!
Jag är dock tveksam om det finns en sån funktion!

(Jag kör MySQL)

heyday

2004-11-09 22:22

om tanken är att varje registerrad ska kunna förekomma en gång i en kategori så är id kolumnen i "kategori-koppling-tabell" överflödig, såvida inte just den relationen ska användas på något annat ställe (vilket inte är troligt i det här fallet). Sätt en unik begränsning på de två kvarvarande istället.

Ett tips.

Robert

2004-11-09 22:39

Citat:

Originally posted by heyday@Nov 9 2004, 23:22
om tanken är att varje registerrad ska kunna förekomma en gång i en kategori så är id kolumnen i "kategori-koppling-tabell" överflödig, såvida inte just den relationen ska användas på något annat ställe (vilket inte är troligt i det här fallet). Sätt en unik begränsning på de två kvarvarande istället.

Ett tips.

Jag tyckte mig läsa att han ville ha ett 1-till-många förhållande.

Tvartom

2004-11-09 22:50

Citat:

Ursprungligen postat av Robert

Citat:

Ursprungligen postat av heyday

Jag tyckte mig läsa att han ville ha ett 1-till-många förhållande.

Nä, det stämmer att den id-kolumnen inte behövs, 1-till många realationen är inte beorende av det!

Tack för tipset!
Körde precis:

Kod:

SQL:

ALTER TABLE kategori-koppling DROP id;

ALTER TABLE kategori-koppling ADD UNIQUE ( register_id, kat_id );

Bör man även alltid ha en PRIMARY ???

Citat:

SQL:
ALTER TABLE kategori-koppling ADD PRIMARY KEY ( register_id, kat_id );

heyday

2004-11-09 23:01

Där ser man vad slow writing kan leda till... Jajja... Vidare.

Jag ser det svårt att lösa det med en generell SQL.
Som jag förstår det så vill du på en och samma rad få ut olika många kategorier beroende på hur många kategorier en registerrad har. Problemet med det tänket är att varje rad måste innehålla exakt lika många kolumner. Och det skulle inte bli fallet om en rad har en kategori, en annan tre.

Det går att lösa, som du är inne på, med en left join. Men då måste du veta innan hur många kategorier det kan finnas för en registerrad innan du ställer frågan.

Enklast gör du det genom att begränsa antalet kategorier en registerrad kan ha, genom att göra en kolumn per kategori. Säg att vi begränsar det till tre.

Då skulle registertabellen (rt i sqlen) se ut som följer:
[id] [namn] [cat1id] [cat2id] [cat3id]

Kategoritabellen (ct i sqlen):
[catid] [namn]

SQLen skulle se ut som följer:

Kod:

SELECT 

 * rt.name

 * ct1.name cat1name

 * ct2.name cat2name

 * ct3.name cat3name

FROM 

 * rt

 * LEFT JOIN ct AS ct1 ON rt.cat1id=ct1.catid

 * LEFT JOIN ct AS ct2 ON rt.cat2id=ct2.catid

 * LEFT JOIN ct AS ct3 ON rt.cat3id=ct3.catid

Som du ser blir SQLen bara längre och längre ju fler kategorier en registerrad kan finnas i, därför måste man veta innan man frågar, hur många det kan finnas.

Det skulle vara möjligt att göra den mer dynamisk. Men jag tror att klockan är för mycket för att jag ska kunna förklara det rätt. Principen är en blandning av den jag förklarat här, och den som diskuterats innan.

EDIT:
Jag är tydligen för långsam idag. Detta är ett svar på Tvartoms meddelande kl 2309 (servern verkar ha sommartid fortfarande)
*vad övrigt är* Nokias "Get togeather do whatever" reklam har ett otroligt skönt soundtrack!

heyday

2004-11-09 23:22

Citat:

Originally posted by Tvartom@Nov 9 2004, 23:50

Bör man även alltid ha en PRIMARY ???

Citat:

SQL:
ALTER TABLE kategori-koppling ADD PRIMARY KEY ( register_id, kat_id );

Primary keys är till för att användas i relationer, som just primary key. Den är alltså i sig unik. Du behöver inte ha en Unique contraint som ser likadan ut som primary. Detta skapar två likadana index, vilket är onödigt.

Anders

2004-11-09 23:29

Citat:

Ursprungligen postat av heyday

Citat:

Ursprungligen postat av Tvartom

Bör man även alltid ha en PRIMARY ???

Citat:

SQL:
ALTER TABLE kategori-koppling ADD PRIMARY KEY ( register_id, kat_id );

Men om man själv inte definierar en KEY så kommer MySQL att själv skapa en (i tysthet) om jag inte minns fel eller missförstod manualen.

Tvartom

2004-11-09 23:30

Tack heyday... Ditt svar gjorde att jag kom på följande lösning:

Kod:

SQL:

SELECT id, namn FROM kat_tbl

Listar alla tillgängliga kategorier, dessa används både i headern till tabellen som skrivs ut, och till att konstruera nästa SQL-fråga:

Kod:

<?PHP

// kategori_id sparade i $kat

$sql = "SELECT register_tbl.*";

foreach ($kat as $value) {

 * $sql .= ", k".$value.".kat_tbl_id";

}

$sql .= " FROM register_tbl";

foreach ($kat as $value) {

 * $sql .= " LEFT JOIN koppling_tbl AS k".$value." ON (k".$value.".register_tbl_id = register_tbl.id AND k".$value.".kat_tbl_id = ".$value.")";

}

?>

Detta bör skapa en SQL-fråga i stil med:

Kod:

SQL:

SELECT

 * register_tbl.*,

 * k1.kat_tbl_id,

 * k2.kat_tbl_id

FROM register_tbl

 * LEFT JOIN koppling_tbl AS k1 ON (k1.register_tbl_id = register_tbl.id AND k1.kat_tbl_id = 1)

 * LEFT JOIN koppling_tbl AS k2 ON (k2.register_tbl_id = register_tbl.id AND k2.kat_tbl_id = 2)

Så får det läggas till lite alias för att skilja dem åt och annat finputs...

Kod:

SQL:

SELECT

 * register_tbl.*,

 * IF(k1.kat_tbl_id IS NULL, 0, 1) AS k1,

 * IF(k2.kat_tbl_id IS NULL, 0, 1) AS k2

FROM register_tbl

 * LEFT JOIN koppling_tbl AS k1 ON (k1.register_tbl_id = register_tbl.id AND k1.kat_tbl_id = 1)

 * LEFT JOIN koppling_tbl AS k2 ON (k2.register_tbl_id = register_tbl.id AND k2.kat_tbl_id = 2)

Fortfarande hade det varit snyggare om det gick att skriva en SQL-fråga direkt utan att dynamsikt generera den via PHP-kod!

heyday

2004-11-10 00:01

Citat:

Men om man själv inte definierar en KEY så kommer MySQL att själv skapa en (i tysthet) om jag inte minns fel eller missförstod manualen.

Jag undrar (efter en snabb koll) det inte är namnen på indexarna du menar. Indexet som en Primary key skapar heter PRIMARY, de övriga döper MySQL till _2 eller vad det stod om man inte döper dom själv. Jag tror inte den skapar något index om jag inte vill ha något.

Robert

2004-11-10 00:14

Känns som om du kanske bör kolla över behovet istället och fundera på varför du måste ha x antal kolumner istället för x antal rader i recordset'et. Vill du slippa räkna kategorierna så kan du slänga in en count i sql'en för antal kategorierna per register om det underlättar? (svårt att ge tips om man inte vet i vilket sammanhang det ska användas)

Då får du som svar typ:

Kod:

Id Register * Kategori * *Antal

1 Kalle * * * *Luleå * * * * 3

1 Kalle * * * *Gävle * * * * 3

1 Kalle * * * *Stockholm *3

2 Svenne * *Kiruna * * * *1

3 Berra * * * Luleå * * * * *2

3 Berra * * * Malmö * * * *2

Anders

2004-11-10 13:52

Citat:

Ursprungligen postat av heyday

Citat:

Men om man själv inte definierar en KEY så kommer MySQL att själv skapa en (i tysthet) om jag inte minns fel eller missförstod manualen.

<!--QuoteBegin--MySQL Manualen[/i]@14.2.6 CREATE TABLE Syntax
A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL will declare them so implicitly (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.[/quote]
Vad nog detta jag tänkte på, men minne är inte vad det borde. :)

Tvartom

2004-11-10 23:00

Robert: Att lista antal är en annan bra idé, som kan användas!

Just nu tänkte jag lista upp register i en <table> och visa tillhörande kategorier:
(Att ha flera kolumner passar utmärkt här...
Typ:

Kod:

<table>

  <tr> <th>Namn</th>  <th>Kat 1</th>  <th>Kat 2</th>  <th>Kat 3</th> </tr>



  <tr> <td>post 1</td>  <td> x </td>    <td>  </td>     <td>  </td> </tr>



  <tr> <td>post 2</td>  <td>  </td>    <td> x </td>    <td> x </td> </tr>

</table>

Jag har många idér till lösningar att stå på nu!
Jag tackar så hemskt mycket för alla svar!

Alla tider är GMT +2. Klockan är nu 19:25.