[mysql] Projekt bazy Opcje

[jachu151]

Witam serdecznie. Mam pytanie do osób doświadczonych w projektowaniu baz danych.

Problem, jak optymalnie zrealizować taki oto system:

- baza ogłoszeń nieruchomości
- cechy do oferty

Różne typy ogłoszeń mieszkania, domy, itd. każdy tych będzie zawierał trochę cech wspólnych trochę różnych. Domyślnie dużo cech.


Czy optymalnym rozwiązaniem będzie stworzenie tabeli:

offer - ogłoszenia, zawierające ID, opis, daty dodania modyfikacji itd
feature_date
feature_text
feature_int ...

osobne tabele dla różnych cech w zależności jakiego typu one będą.

Wyszukiwanie będzie zawierać dużo LEFT JOINOW, jeśli będę chciał znaleźć ofertę o 10 cechach to jest 10 joinów, da się to zrobić optymalniej? Z mniejszą zależnością od ilości wyszukiwanych cech?



Pozdrawiam
jachu

[bpskiba]

Rozsądne wydaje się stworzenie trzech tabel
1 nieruchomości
2 cechy_nieruchomości
3 oferty

[mmmmmmm]

Możesz zrobić tak:
tabela ogłoszenia:
ogloszeniaID, opis, cena...
tabela cechy:
cechyID, ogloszeniaID, opis_cechy, wartosc_cechy

i w cechach dajesz np:
1, 1, "kolor", "czerwony"
2, 1, "wysokość", "niski"
3, 1, "głośność", "średni"
(UNIQUE powinieneś ustawić na ogloszeniaID+opis_cechy)
opis_cechy możesz zrobić na słowniku, ale szkoda zachodu - zapytania będą skomplikowane

Aby znaleźć ogłoszenie, które spełnia twoje wszystkie cechy robisz:

[SQL] pobierz, plaintext 
SELECT * FROM ogloszenia WHERE ogloszeniaID=(SELECT ogloszeniaID FROM cechy JOIN (SELECT "kolor" cecha, "zielony" wartosc UNION SELECT "typ","kombi") wybrane ON cechy.cecha=wybrane.cecha AND cechy.wartosc=wybrane.wartosc GROUP BY 1 HAVING Count(id)=2 /* 2 bo dwie cechy ustawiłeś */)
[SQL] pobierz, plaintext 

Ten SQL daje ci duże pole do popisu

[jachu151]

Tylko należałoby pamiętać o tym, że w cechach mogą być liczby, daty, stringi ... gdybym trzymał w varchar wszystkie wartości to byłby problem z sortowaniem (używanie CAST raczej będzie mało wydajne).

[SQL] pobierz, plaintext 
SELECT offer.id, offer.description, sort.value FROM offer
LEFT JOIN offer_feature f ON offer.id=f.offer_id
LEFT JOIN offer_feature sort ON offer.idort.offer_id AND sort.feature_id=10
WHERE (f.feature_id=idMiasto AND f.value='Miasto')
OR (f.feature_id=idDzielnica AND f.value='Dzielnica')
GROUP BY offer.id
HAVING COUNT(offer.id)=2
ORDER BY[b] CAST(sort.value AS decimal(14, 2))[/b] DESC
LIMIT 5
OFFSET 5
[SQL] pobierz, plaintext 

Czy takie zapytanie jest bardziej optymalne niż X LEFT JOINow - gdzie x to liczba sprawdzanych cech?

[alegorn]

dla mnie projektowanie bazy danych zaczyna się od kartki i ołówka..
rozpisuję wszystkie tabele, pola, relacje. potem analizuję czy wszystko jest ok, dążę do 3nf. to w 90% wystarcza.
kolejny etap - to projektowanie już w np w workbench ustawianie constrainow, fk, primary, prawidłowe wyodrębnienie/definiowanie encji..

najważniejszym etapem, jest chyba wyodrębnienie głównej encji, rzeczywiste primary key.. pisanie zapytań na tym etapie uważam za grube nieporozumienie

j.

[jachu151]

Tylko normalizacja wiążę się często z późniejszą wydajnością. Za wszelką cenę nie mogę dążyć do tego, gdyż później wydajnościowo to nie spełni zadania.

Pytanie zadaję do osób, które miały podobny problem/rozważanie i co okazało się lepsze w perspektywie czasu.

tabela definicji cech:

features
feature_id | name | type

offer
offer_id | description | create_time ...

to jest chyba jasne i dobrze, pytanie jest w tym momencie:


offer_features
id | feature_id | offer_id | value


czy

offer_features_int
id | feature_id | offer_id | value

offer_features_float
id | feature_id | offer_id | value

offer_features_text
id | feature_id | offer_id | value

offer_features_date
id | feature_id | offer_id | value



Czy może inaczej?


Danych dla każdej oferty będzie wiele cech, po każdej powinna być możliwość sortowania, przeszukiwania.

Ten post edytował jachu151 12.07.2012, 11:21:01

[alegorn]

normalizacja przyczyna spadku wydajnosci? noo, ja bym raczej powiedzial cos znacznie innego, no ale ro Twoje dzielo ma byc.

co do :: offer_features
powiedz mi jakie klucze zakladasz na ta tabele?
o ile widze zamierzasz zalozyc klucz primary i autoinc na ID, byc moze do tego dolozysz unika na feature_id, offer_id

ale ja sie zapytam... poco ?
wywal pole id. zaloz primary na feature_id, offer_id
zysk poza wielkoscia tabeli - to jeden ZBEDNY index mniej, czyli zysk na update/insert w tabeli. to pole naprawde nie ma racji bytu tutaj..

swoja droga o co biega z tabelami offer_features_* ? tzn z ich iloscia..


j.

[jachu151]

normalizacja przyczyna spadku wydajnosci? noo, ja bym raczej powiedzial cos znacznie innego, no ale ro Twoje dzielo ma byc.

No dokładnie, np pojedyncza tabela będzie szybsza niż tabela offer i features

ale ja sie zapytam... poco ?
wywal pole id. zaloz primary na feature_id, offer_id

Masz zdecydowanie rację

swoja droga o co biega z tabelami offer_features_* ? tzn z ich iloscia..

Tak jak pisałem cechy mogą być różnych typów.

Jeśli np cechą będzie cena to jako varchar nie będzie posortowana właściwie np

Składowanie daty, float, int w bazie jako varchar to chyba nie najlepszy sposób?

Ten post edytował jachu151 12.07.2012, 21:07:26

[alegorn]

eh, silniki relacyjnych baz danych naprawdę dobrze sobie radzą z relacjami o ile oczywiście prawidłowo porobisz złączenia. kwestia operacji jakie będą wykonywane na tej bazie. być może właśnie pracujesz nad przyszłym bólem głowy

hm. co do cech..
a co w przypadku jeśli będziesz chciał dodać kolejną cechę ? będziesz dodawał kolejną tabelę? bez sensu.
ja raczej bym stawiał na tabelę (jeśli naprawdę potrzebujesz znać typ tej cechy):


offer_features
feature_id | offer_id | value | typ_cechy_fk

****
tak naprawdę wydaje mi sie ze to i tak nie jest najlepszym rozwiązaniem...

ja, tak naprawdę wyodrębniłbym cechy wspólne dla wszystkich ogłoszeń (np data, miejscowość itp..) do jednej tabeli.
w tej tabelce koniecznie umieściłbym wszystkie pola po których bym przewidywał sortowanie.
pozostałe cechy, które nie są standardowe - trzymałbym w dodatkowej tabeli - ale nie dawałbym możliwości sortowania po nich.
projektując ta tabele - zgodnie z normalizacja i optymalizacja, starałbym się unikać pol zmiennej długości, np.: zamiast nazwy miejscowości - FK do tabeli z słownikiem miejscowości.

pamiętaj, że pierwszym przykazaniem optymalizacji jest stała długość WSZYSTKICH pól, oraz minimalna długość danych. i to jest chyba cel ku któremu powinieneś dążyć.
zachowanie maksymalnej unikatowości - także jest jest jedną z ważniejszych cech.
jeśli zrobisz do tego prawidłowe indexy - będzie śmigać, i to o wiele lepiej niż jeden wielki worek w którym masz wszystko.... wszystko i nic.

j.

PS ale do tego normalizacja jest potrzebna :] wyobraź sobie zliczanie counta dla nieznormalizowanej tabeli.... tożto koszmar

[jachu151]

hm. co do cech..
a co w przypadku jeśli będziesz chciał dodać kolejną cechę ? będziesz dodawał kolejną tabelę? bez sensu.

Nie w ten sposób. Typy _date , _float, _int, _text przykładowo i więcej już nie będzie. Czyli przykładowo miejscowość, dzielnica, ulica .. po np okolice, ogrzewanie itd byłyby w _text, w _float byłyby powierzchnie wszelkie, ceny itd, w _int ilość pokoi, pomieszczeń, łazienek itd


Z tym przeniesieniem cech do sortowania do osobnej tabeli to jest chyba dobra myśl, tylko dla różnych typów oferty mogą być różne pola do sortowania... Czy tworzyć x osobnych tabel? Czy po prostu jedną z możliwymi pustymi kolumnami?



Dla przyśpieszenia odczytów zastanawiam się również nad trzymaniem wszystkich cech w JSON w tabeli oferta (służąca do wyświetlania), czy jest to dobry pomysł? Zakładając, że na tabeli cech nie ma ich edycji (przy aktualizacji są usuwane, następnie dodawane), więc ze spójnością danych nie byłoby problemu. Interesują mnie rozwiązania na dużych systemach, jak się je projektuje, żeby działało wydajnie