[mysql] opzet tabel (met veel keuzes)

[Zynth]

Ik zit met een vraag waar ik eigenlijk geen goed antwoord op heb.
Misschien bestaat er wel geen "goed" antwoord, maar dan zou ik toch graag het béste antwoord willen.

Het probleem is dat ik voor een projectje even een tabel moet ontwerpen.
Een gebruiker krijgt de vraag:
Wat voor gebouwen heb je?
Daar kan hij kiezen uit een hele lijst (woning, schuurtje, garage, en-nog-20)
Hij kan hierin zoveel dingen aangeven als hij wil.
Vervolgens hoort er bij elk object wat hij aangeeft opgegeven te worden uit welk jaartal het komt en wat de "staat" is.

Het probleem is nu dat ik niet weet hoe ik de SQL hiervan op moet zetten.
Het is namelijk zo dat er ook op gezocht moet kunnen worden, dus alles in een array zetten en die serializen is geen optie.
Verder is het eigenlijk geen optie om gewoon 20*3 velden aan te maken en die eventueel maar te vullen zodra er genoeg opties zijn gekozen. Dan is het zoeken namelijk onbegonnen werk.
(zodra iemand wil zoeken naar iemand die een schuur heeft, moet je dan een query maken met WHERE item1='schuur' OR item2='schuur'.....door tot item20)

Een andere oplossing is voor elk item 3 vaste velden aan te maken, en die pas te gebruiken als hij ook daadwerkelijk dat object heeft. Maar dan wordt de tabel weer ranzig groot. (naast de 20*3 opties, krijgt hij nog meer vragen met eenzelfde strekking... een tabel van 180 kolommen lijkt me ook niet goed).

Er zijn vast mensen die zoiets eerder hebben gehad, laat je horen!

[DeverauX]

Twee tabellen? Een met een ID, de omschrijving van het type gebouw en deze vervolgens op ID koppelen aan een tabel met info over het gebouw (leeftijd, staat etc)?

[Verwijderd]

Wat ook handig is een optie "anders" onder de select box te geven wat mensen daar invullen zet je gewoon ook in je tabel. vullen je klanten je tabel vanzelf aan (wel blijven controleren)

[CH4OS]

Je hebt dus 3 tabellen nodig
1) Tabel met alle type gebouwen er in (woning, schuurtje, garage, en-nog-20)
2) Tabel met 'situaties'; hierin komen dus de records te staan welke gebouwen er in zitten en dergelijke
3) Tabel met (huidige staten); deze knoop je dan aan het id van het gebouw en het id van de situaties tabel...

Hoe je het verder in moet delen moet nu denk ik wel lukken, zeker met normalisatie

[ Voor 8% gewijzigd door CH4OS op 06-06-2006 09:56 ]

[DeverauX]

@b83s: Handig? Wellicht. Verstandig? Lijkt me niet.

En vergeet de indexen niet...met de zoekopdrachten kan dat aardig wat tijd schelen...

[ Voor 54% gewijzigd door DeverauX op 06-06-2006 09:54 ]

[Verwijderd]

@deveraux: idd verstandig is het niet maar wel makkelijk misschien een handig tooltje om wat overzicht te krijgen van je tabellen.

http://fabforce.net/dbdesigner4/

[ToolkiT]

DeverauX schreef op dinsdag 06 juni 2006 @ 09:45:
Twee tabellen? Een met een ID, de omschrijving van het type gebouw en deze vervolgens op ID koppelen aan een tabel met info over het gebouw (leeftijd, staat etc)?

idd:

Tabel gebouwen met velden:
gebouw_ID *
gebouw_type
bouwjaar
Staat

Tabel gebouwtypes met velden
Type ID*
beschrijving_type
(eventueel andere gegevens over een type)

Vervolgens kan je die 2 tabellen joinen om alle gegevens te krijgen:
select
A.gebouw_ID *
B.beschrijving_type
A.bouwjaar
A.Staat
from gebouwen A, gebouwtypes B
Where A.gebouw type=B.Type ID

[NMe]

De opzet van databasestructuren mag je bespreken in Software Engineering & Architecture, zoals je ook had kunnen lezen in Waar hoort mijn topic?

PRG>>SEA

[Zynth]

bedankt voor de hulp!

ik zit nu wel met een nieuw probleem

stel ik heb nu tabel A met advertenties, en tabel B met de gebouwen:
A: id (int), advertentietekst (mediumtext)
B: id (int), gebouwtype (varchar)

En nu wil ik zoeken naar alle advertenties die een "huis" en een "schuur" bevatten.

Maar: SELECT * FROM A,B WHERE A.id=B.id AND B.gebouwtype='schuur' AND B.gebouwtype='huis'
werkt natuurlijk niet, omdat B.gebouwtype niet 2 verschillende waardes tegelijk kan hebben, logisch.
Maar, hoe kan ik nu een query bouwen die wél op een willekeurig aantal gebouwen kan zoeken?

[Varienaja]

Ik denk zoiets:

code:

select * from A where id in (
select id, count(*) from B where type=schuur of type=huis
group by id
having count(*)=2
)

[ Voor 68% gewijzigd door Varienaja op 28-06-2006 15:08 . Reden: beter lezen ]

[Creepy]

Of zoek je alle advertenies die of een schuur of een huis bevatten? In dat geval is je laatste and een OR en moet je nog een set haakjes om de gehele OR plaatsen zodat dat eerst geevalueerd word.

code:

SELECT * FROM A,B WHERE A.id=B.id AND (B.gebouwtype='schuur'  or B.gebouwtype='huis')

Maaruh, al eens een fatsoenlijke SQL tutorial gedaan? Daarin wordt dit soort logica ook wel uitgelegd

[Zynth]

bedankt!
ik heb inderdaad al veel sql tutorials gedaan, en er ook een paar vakken over gevolgd
Maar dit probleem ben ik nog niet eerder tegengekomen, en ik weet er ook geen oplossing voor eigenlijk.
@creepy: het moet wel AND zijn, dat is het probleem.
@varienaja: dat is geen oplossing die voldoet. Er zijn namelijk nog veel meer tabellen die mee gaan doen, zoals een tabel kamers, kleuren, etc, dus geneste queries worden dan een regelrechte ramp.

Of heeft iemand een beter idee voor een heel andere database-opzet waarmee dit soort dingen wel kunnen?

[NetForce1]

Volgens mij ben je op zoek naar een koppeltabel waarin je gebouwen koppelt aan een advertentie.

[Zynth]

ja, dat klopt. Maar hoe doe ik dat

[Varienaja]

Zynth schreef op woensdag 28 juni 2006 @ 17:55:
bedankt!
@varienaja: dat is geen oplossing die voldoet. Er zijn namelijk nog veel meer tabellen die mee gaan doen, zoals een tabel kamers, kleuren, etc, dus geneste queries worden dan een regelrechte ramp.

Voor wat betreft al die combinaties heb ik misschien een ideetje.

Stel, je totale ruimte omvat [schuur, huis, kippenhok, duiventil, dakkapel]
Sla iedere deelverzameling hiervan die je in je database hebt apart op met z'n eigen id.

Als je nu gaat zoeken op [huis, kippenhok, dakkapel] dan zoek je eerst in je deelverzamelingentabel naar het id van de deelverzameling die precies die 3 items bevat. Eventueel heb je al die deelverzamelinge in je geheugen (hopelijk zijn het er geen miljoenen )
Zodra je het id van de betreffende deelverzameling hebt kan je eenvoudigweg prikken:
select * from A where deelverzamelingid=hetgevondenid

Hopelijk heb je er wat aan; ik gebruik deze methode zelf om setjes van keywords aan muziek-bestanden te koppelen.

[NetForce1]

Het is idd niet zo triviaal als het op het eerste gezicht leek. Ik ben wat aan het rommelen geweest en ben uiteindelijk op de volgende query gekomen:

SQL:

SELECT A.* 
FROM Advertentie A
WHERE NOT EXISTS (
    SELECT *
    FROM Gebouw G
    WHERE G.gebouwtype IN ('huis', 'schuur')
    AND G.gebouwtype NOT IN (
        SELECT G2.gebouwtype
        FROM (Gebouw G2 INNER JOIN AdvertentieGebouwen AG2 ON G2.ID = AG2.gebouwId) INNER JOIN Advertentie A2 ON AG2.advertentieId = A2.ID
        WHERE A.ID = A2.ID
    )
);

In regel 4-6 wordt een set opgebouwd die alle gevraagde types bevat, vervolgens worden daar in de regels 7-11 de types vanaf getrokken van gebouwen die horen bij de advertentie die de db nu aan het bekijken is. Als de resulterende set leeg is heeft de advertentie tenminste de gevraagde gebouwen en mag hij toegevoegd worden aan het resultaat.

.edit:
Ik zie net dat je liever geen subqueries wilt, maar volgens mij ga je daar niet aan ontkomen zonder je heil te zoeken in hacks als hier: [rml]Varienaja in "[ mysql] opzet tabel (met veel keuzes)"[/rml] gepost. Als je de query in je programmacode een beetje netjes opbouwt is het hebben van veel subqueries niet zo'n probleem lijkt me. (Afgezien van evt. performance problemen in je rdbms, maar daar kan ik geen gefundeerde uitspraken over doen)

[ Voor 24% gewijzigd door NetForce1 op 29-06-2006 00:53 ]

[Zynth]

bedankt, vooral netforce met een vrij aardig idee.
Het probleem is dat "gebouwen" niet de enige tabel met opties is.
Het is maar een van de vele.
Een advertentie bestaat namelijk uit een "advertentie", een selectie aan "gebouwen", een selectie aan "grond", "kamers", "keukenapparatuur", etcetc.
Op zo'n manier dan naar een advertentie gaan zoeken die een match oplevert, is dan zo goed als onmogelijk

Nogmaals, hoe doen makelaarssites dit bijvoorbeeld?
Want je wil heel veel kunnen opslaan, bij agrarisch bijvoorbeeld welke typen vee en hoeveel daarvan.
Maar daar moet je natuurlijk wel op kunnen zoeken (ik wil minstens 20 koeien en maximaal 40 varkens).
Wat is de industriële oplossing van dit probleem?

[ Voor 34% gewijzigd door Zynth op 29-06-2006 10:58 ]

[NetForce1]

Ik denk dat je beter op zoek kunt gaan naar een oplossing met een zoekmachine als lucene oid.

[Boogie]

Of je gaat met fulltext search werken (mysql).
Als de "omschrijving" een verhaaltje is, dan geeft hij de meest waarschijnlijke resultaten terug.
Ik weet niet hoe het invoeren werkt, maar als niet alles met keuzelijstjes werkt is dat ook een mooie oplossing.