[PostgreSQL][PHP] Problem z zapytaniem, projekt bazy danych Opcje

[Xenom]

Korzystam z bazy danych postgresql, ale używam poradników z mysql i w razie błedów zapytań staram się poszukać informacji w google.
W poniższym przypadku trafiłem na nadmiar informacji i tutaj poproszę o pomoc. Przedstawię najlepiej cały kod z zakomentowaniem linijek dających błąd, to będzie mówić samo za siebie i więcej niż moje amatorskie wyjaśnienie.
Jest to mój pierwszy projekt bazy danych/php i za krytyczne uwagi będę wdzięczny (varchary nie są skonfigurowane).

[PHP] pobierz, plaintext 
<?php
require "db_conf.php";
require "db_connect.php";
 
$create_users_table = pg_query('CREATE TABLE users (
	id_user BIGINT NOT NULL,                        //w tym wypadku nie SERIAL bo dla zwykłego usera bedzie korzystanie z konta facebook
	money FLOAT DEFAULT '0',
	name VARCHAR(20) DEFAULT 'none',
	mail VARCHAR(20) DEFAULT 'none',
	PRIMARY KEY (id_user)
	)');
 
$create_companies_table = pg_query('CREATE TABLE companies (
	login VARCHAR(20) NOT NULL,                //też nie SERIAL, tylko login na pr.key, poniewaz wiekszosc zapytan bedzie szukac po loginie
	company_name VARCHAR(20) NOT NULL,
	regon VARCHAR(20) NOT NULL,
	nip VARCHAR(20) NOT NULL,
	krs VARCHAR(20) DEFAULT 'none',
	director VARCHAR(20) NOT NULL,
	adress VARCHAR(20) NOT NULL,
	city VARCHAR(20) NOT NULL,
	state VARCHAR(20) NOT NULL,
	phone VARCHAR(20) NOT NULL,
	mail VARCHAR(20) NOT NULL,
	password VARCHAR(20) NOT NULL,
	money FLOAT DEFAULT '0',
	active BOOL DEFAULT '0',
	PRIMARY KEY (login)
	)');
 
$create_products_table = pg_query('CREATE TABLE products (
	id_product BIGINT SERIAL NOT NULL,     //bigint bo moze byc z czasem cala masa produktów/ jest cos takiego jak unsigned w cpp?
	login_seller VARCHAR(20) NOT NULL,
	normal_price FLOAT NOT NULL,
	promotion_price FLOAT NOT NULL,
	product_name VARCHAR(20) NOT NULL,
	description text NOT NULL,
	product_city VARCHAR(20) NOT NULL,
	start_date VARCHAR(20) NOT NULL,
	day_count INT NOT NULL,
	photo VARCHAR(20) NOT NULL,
	PRIMARY KEY (id_product),
	KEY (login_seller)                             //blad tutaj przy zapytaniu
	)');
 
$control_table = pg_query('CREATE TABLE control ( //ta tabela kotroluje jaki user, jaki produkt zakupil
	id_user BIGINT NOT NULL,
	id_product BIGINT NOT NULL,
	date VARCHAR(16) NOT NULL
	INDEX ('id_user','id_product')           //blad tutaj przy zapytaniu
	)');
?>
[PHP] pobierz, plaintext 

Ten post edytował Xenom 18.04.2012, 12:27:34

[czachor]

Podaj komunikaty błędów, które się wyświetlają.

[bpskiba]

1.W przedostatniej linijce brakuje przecinka na końcu
2. W ostatniej linijce nie Index, a key lub Foreign key i brak definicji nazwy indexu

[Xenom]

Ok dzięki za radę, poradźcie mi teraz, czy tak wykonana db z referencjami jest dobrą konstrukcją, czy referencje poniżej nie powinny być umieszczane?

[PHP] pobierz, plaintext 
<?php
require "db_conf.php";
require "db_connect.php";
 
$create_users_table = pg_query('CREATE TABLE users (
	id_user BIGINT NOT NULL,
	money FLOAT DEFAULT '0',
	name VARCHAR(20) DEFAULT 'none',
	mail VARCHAR(20) DEFAULT 'none',
	PRIMARY KEY (id_user)
	)');
 
$create_companies_table = pg_query('CREATE TABLE companies (
	login VARCHAR(15) NOT NULL,
	company_name VARCHAR(50) NOT NULL,
	regon VARCHAR(20) NOT NULL,
	nip VARCHAR(20) NOT NULL,
	krs VARCHAR(20) DEFAULT 'none',
	director VARCHAR(40) NOT NULL,
	adress VARCHAR(40) NOT NULL,
	city VARCHAR(20) NOT NULL,
	state VARCHAR(20) NOT NULL,
	phone VARCHAR(16) NOT NULL,
	mail VARCHAR(30) NOT NULL,
	password VARCHAR(20) NOT NULL,
	money FLOAT DEFAULT '0',
	active BOOL DEFAULT '0',
	PRIMARY KEY (login)
	)');
 
$create_products_table = pg_query('CREATE TABLE products (
	id_product SERIAL NOT NULL,
	login_seller VARCHAR(15) NOT NULL REFERENCES companies(login),
	normal_price FLOAT NOT NULL,
	promotion_price FLOAT NOT NULL,
	product_name VARCHAR(20) NOT NULL,
	description text NOT NULL,
	product_city VARCHAR(20) NOT NULL,
	start_date VARCHAR(16) NOT NULL,
	day_count INT NOT NULL,
	photo VARCHAR(50) NOT NULL,
	PRIMARY KEY (id_product)
	)');
 
$control_table = pg_query('CREATE TABLE control (
	id_user BIGINT NOT NULL REFERENCES users(id_user),
	id_product BIGINT NOT NULL REFERENCES products(id_product),
	date VARCHAR(16) NOT NULL
	)');
?>
[PHP] pobierz, plaintext 

Ten post edytował Xenom 19.04.2012, 07:34:56

[bpskiba]

Po mojemu jest to bardzo zła konstrukcja.
1. Kwestia porządku: Jeżeli tworzysz tabelę users, to kluczem powinno być id_users. Dla tabeli products id_products
2. Dla kluczy głównych obowiązkowo autoincrement
3. Za pomysł uczynienia kluczem głównym pola VARCHAR w tabeli companies należy Cię wybatożyć
4. Pola na pieniądze nie float, a numeric lub decymal
5. Pole na datę nie typu Varchar, a np DATETIME
6. Brak powiązań między tabelami
7. Takie dane jak telefon lepiej przechowywać w polach INT
8. Za mało kluczy zewnętrznych wymuszających więzy integralności
9. Widzę pole start_date. Jeżeli masz zamiar określać odstępy między datami, to obowiązkowo typ Timestamp
10. NIP, regon to pola typu INT (pomyśl jak będzie wyglądać wyszukiwanie po numerze nip)

W ramach higieny języka polskiego zapytam czemu te nazwy tabel są po angielsku.
Na osłodę dodam, że to są typowe błędy, które popełniał każdy (ze mną włącznie)

[Xenom]

Po mojemu jest to bardzo zła konstrukcja.
1. Kwestia porządku: Jeżeli tworzysz tabelę users, to kluczem powinno być id_users. Dla tabeli products id_products
2. Dla kluczy głównych obowiązkowo autoincrement
3. Za pomysł uczynienia kluczem głównym pola VARCHAR w tabeli companies należy Cię wybatożyć
4. Pola na pieniądze nie float, a numeric lub decymal
5. Pole na datę nie typu Varchar, a np DATETIME
6. Brak powiązań między tabelami
7. Takie dane jak telefon lepiej przechowywać w polach INT
8. Za mało kluczy zewnętrznych wymuszających więzy integralności
9. Widzę pole start_date. Jeżeli masz zamiar określać odstępy między datami, to obowiązkowo typ Timestamp
10. NIP, regon to pola typu INT (pomyśl jak będzie wyglądać wyszukiwanie po numerze nip)

W ramach higieny języka polskiego zapytam czemu te nazwy tabel są po angielsku.
Na osłodę dodam, że to są typowe błędy, które popełniał każdy (ze mną włącznie)

Serdeczne dzięki, już piszę dlaczego, co i jak zrobiłem i też mam pytania.
1. tabela użytkowników będzie podłączona do facebooka i robiłem ja z zamysłem, że podczas logowania użytkownik będzie szukany
właśnie po tym id, w sensie że nadawanie jako główny klucz nowego id spowolni wyszukiwanie danych po logowaniu. Czy to dobre podejście, czy jednak
zwykły SERIAL i nowe id dodatkowo jako klucz główny?
2. autoincrement to nie jest przypadkiem SERIAL w postgresql?
3. Podczas logowania firm, czy wyświetlania formularzy bedzie wykorzystywany login, czy id_firma będzie lepsze. Moja logika polega na tym, że skrypt szuka tylko loginu
i nie musi sortować wszystkich danych(rozumiem, że klucz główny powoduje takie sortowanie), teraz jakbym miał znaleźć firme po id to DB musi jechać każde id pokolei.
Jak ten tok myślenia jest zły to też napiszcie, żebym zrozumiał dlaczego.
6. Nie bardzo rozumiem, mam pewne wyobrażenie jak ta aplikacja ma działać i na bazie czego pobierać dane z DB, to dałem pod tabelę control. W jakim sensie brak powiązań?

Resztę rozumiem, w każdym razie dzięki za rady.
Co do pytania o angielskie nazewnictwo, to jest to pewne przyzwyczajenie, mimo że mam taki sobie angielski. Od jakiegoś czasu zajmuję się tworzeniem gier free(aktualnie na fonline engine, angel script, który bardzo podobny jest do cpp) i współpracowałem z osobami z wielu krajów, dlatego ten angielski na siłę wbijam :-)

Co do tworzenia powyższej aplikacji to nie mam absolutnie doświadczenia w tej działce. Jest to aplikacja na życzenie szefa(choć firma nie zajmuje się programowaniem), więc w pracy muszę bardziej działać i staram się to jakoś ogarnąć, jednak wolę poświęcić dużo więcej czasu na fundamentalne konstrukcje typu DB i zrobić to jakoś przyzwoicie z Waszą pomocą.
Inna sprawa, że świetnie się przy tym bawię, a dodatkowo uczę nowych rzeczy, co daje mi przyjemność i radochę z pracy.

ps. Tu taki przykład gry http://www.youtube.com/watch?v=ZjA2wBU10z8...hwgWSDJZYVInzg= , gdzie właśnie z wykorzystaniem fonline engine staramy się przenieść realia fallouta 2 i tactics na mmo :-)

[bpskiba]

Serdeczne dzięki, już piszę dlaczego, co i jak zrobiłem i też mam pytania.
1. tabela użytkowników będzie podłączona do facebooka i robiłem ja z zamysłem, że podczas logowania użytkownik będzie szukany
właśnie po tym id, w sensie że nadawanie jako główny klucz nowego id spowolni wyszukiwanie danych po logowaniu. Czy to dobre podejście, czy jednak
zwykły SERIAL i nowe id dodatkowo jako klucz główny?
2. autoincrement to nie jest przypadkiem SERIAL w postgresql?
3. Podczas logowania firm, czy wyświetlania formularzy bedzie wykorzystywany login, czy id_firma będzie lepsze. Moja logika polega na tym, że skrypt szuka tylko loginu
i nie musi sortować wszystkich danych(rozumiem, że klucz główny powoduje takie sortowanie), teraz jakbym miał znaleźć firme po id to DB musi jechać każde id pokolei.
Jak ten tok myślenia jest zły to też napiszcie, żebym zrozumiał dlaczego.
6. Nie bardzo rozumiem, mam pewne wyobrażenie jak ta aplikacja ma działać i na bazie czego pobierać dane z DB, to dałem pod tabelę control. W jakim sensie brak powiązań?

Ad. 1 Dwa problemy
- facebook jak i wiele innych ma możliwość zmiany loginu do konta. Co wtedy zrobisz?
- SELECT name,date FROM control JOIN users USING(id_user) Czyli podstawowe złączenie przy większej ilości rekordów będzie zwalniać gdyż porównywanie tekstów test kosztowne
Ad.2
Tego nie wiem
Ad. 3
Źle kombinujesz, ale radzę na początek nie zajmować się sposobem działania optymalizatora bazy danych. Wystarczy przyjąć, że sposób wyszukiwania zależy od konstrukcji pytania sql oraz indeksów, najszybsze są operacje na liczbach stałoprzecinkowych, a najwolniejsze jest porównywanie tekstów.
Ad. 6
Konstrukcja bazy ma być taka, że każde zapytanie sql będzie używać indeksu i nie będzie możliwe wpisanie danych powodujących niespójność bazy. Przykładowo zdefiniowanie klucza obcego id_user not null w tabeli control uniemożliwi dodanie rekordu do tej tabeli jeżeli w tabeli user nie będzie rekordu o odpowiednim id.
Nie da się też usunąć rekordu z tabeli users jeżeli będą rekordy o takim id w tabeli control.
W Twoim schemacie tabela companies nie jest powiązana z niczym, więc trudno mówić o indeksach i spójności.