[es] - Greska u dizajnu tabela ili nepravilnom upitu?

ngj
Srbija

Član broj: 64117
Poruke: 17
*.dynamic.isp.telekom.rs.

Profil

Greska u dizajnu tabela ili nepravilnom upitu?

^{06.08.2010. u 22:10 - pre 167 meseci}

Imao bih dva pitanja. Prvo pitanje je da li sam negde pogresio u dizajnu baze? Drugo pitanje, kako da dobijem rezultate bez da se dupliraju?

Trenutno je ovakva situacija.
Imam listu clanova.
Svaki clan moze, a i ne mora da ima broj(eve) telefona. Ako ima telefon, moze da ima vise od jednog.
Svaki clan moze, a i ne mora da zna strani(e) jezik(e). Ako zna, moze da zna vise od jednog.

Napravio sam cetiri tabele:

Code:

=====================================
Table: clanovi
------------------------------------------------
Field    Type    Null    Default    Comments
------------------------------------------------
id    int(10)    No
ime    varchar(250)    No
prezime    varchar(250)    No
deleted    enum('Y', 'N')    No     N
=====================================

=====================================
Table: broj_telefona
------------------------------------------------
Field    Type    Null    Default    Comments
------------------------------------------------
id    int(10)    No
clanovi_id    int(10)    No
tip    enum('kucni', 'poslovni', 'mobilni', 'inostrani')    No     mobilni
broj_telefona    varchar(50)    No
komentar    varchar(250)    No
=====================================

=====================================
Table: lista_jezika
------------------------------------------------
Field    Type    Null    Default    Comments
------------------------------------------------
id    tinyint(3)    No
naziv    varchar(250)    No
=====================================

=====================================
Table: clanovi_jezik
------------------------------------------------
Field    Type    Null    Default    Comments
------------------------------------------------
id    int(10)    No
clanovi_id    int(10)    No
lista_jezika_id    tinyint(3)    No
nivo    enum('osnovni', 'srednji', 'viši')    No     osnovni
komentar    varchar(250)
=====================================

FOREIGN KEYS
lista_jezika_id pokazuje na tabelu lista_jezika, polje id
clanovi_id pokazuje na tabelu clanovi, polje id

dampovana baza je na kraju posta.

Ono sto ja zelim je da u jednom upitu, dobijem listu korisnika (vise od jednog reda, ali da bude jedan red jedan korisnik).U jednom redu dobijam polja ime, prezime, brojevi telefona (iz tabele brojevi telefona, dve kolone broj_telefona, tip, smestam kao string), jezik (iz tabela clanovi jezik nivo, i iz tabele lista jezika naziv, smestam u obliku strina).Ali bez da mi se dupliraju podatci.

Da krenem redom.
Ono sto mogu je, da tako nesto dobijem, samo ako izvlacim ILI brojeve ILI jezik zajedno za clanovima. Ukoliko pokusam i jedno, i drugo, dobijam duplirane podatke.

recimo to ovako izgleda samo ako obuhvatim tabelu clanovi i broj_telefona

za upit samo sa clanovima i brojevima telefona, komandom

Code:

SELECT c.id, c.prezime, c.ime, GROUP_CONCAT( CONCAT( '[', b.tip, '-z-', b.broj_telefona, ']' )
SEPARATOR "-x-" ) AS telefoni
FROM clanovi AS c
LEFT JOIN broj_telefona AS b ON c.id = b.clanovi_id
WHERE c.deleted = 'N'
GROUP BY c.id
ORDER BY c.prezime DESC
LIMIT 0 , 30

dobijam
sledeci rezultat
=====================================
6 Šuštić Nikodije [kućni-z-8240679]-x-[mobilni-z-+382-62-5554443]-x-[poslovni-z-77777777]-x-[mobilni-z-065-888888888]-x-[kućni-z-444444]
------------------------------------------------
5 Petrović Pera [mobilni-z-0655542526]-x-[kućni-z-018-756721]-x-[poslovni-z-018-200562]
------------------------------------------------
20 Nikolić2 Jelena [kućni-z-4230679]-x-[mobilni-z-091-555555555]-x-[poslovni-z-11223344]-x-[mobilni-z-069-3459589]
------------------------------------------------
19 Nikolić Nikola [kućni-z-011-5265356]-x-[poslovni-z-023-44444444]-x-[mobilni-z-061-99999999]-x-[mobilni-z-063/5689898]-x-[kućni-z-011-5265356]-x-[poslovni-z-023-44444444]-x-[mobilni-z-06
------------------------------------------------
8 Mitić Mića NULL
=====================================

Crveni deo je onaj koji mi je zanimljiv da dobijem. U ovom slucaju to je ono sto sam ocekivao.
Kada to isto probam sa jezicima, a bez da trazim i brojeve telefona, dobjam takodje zadovoljavajuci razultat.

evo coda

Code:

SELECT c.id, c.prezime, c.ime, GROUP_CONCAT( CONCAT(  '[', lj.naziv,  '-z-', j.nivo,  ']' )
SEPARATOR  "-x-" ) AS jezik
FROM clanovi AS c
LEFT JOIN clanovi_jezik AS j ON c.id = j.clanovi_id
LEFT JOIN lista_jezika AS lj ON j.lista_jezika_id = lj.id
WHERE c.deleted =  'N'
GROUP BY c.id
ORDER BY c.prezime DESC
LIMIT 0 , 30

evo i rezultata

=====================================
6 Šuštić Nikodije [španski-z-osnovni]
------------------------------------------------
5 Petrović Pera NULL
------------------------------------------------
20 Nikolić2 Jelena NULL
------------------------------------------------
19 Nikolić Nikola [engleski-z-viši]-x-[ruski-z-srednji]-x-[nemački-z-osnovni]
------------------------------------------------
8 Mitić Mića [nemački-z-osnovni]-x-[arapski-z-viši]
=====================================

Sada bih to isto zeleo, samo da imam i brojeve telfona i jezike
Tu vec dolazi do dupliranja, tripliranja podataka,....

evo komande koju sam probao (isprobavao sam razlicite varijante, i sl. ali nikako da ....)

Code:

SELECT c.id, c.prezime, c.ime, GROUP_CONCAT( CONCAT( '[', b.tip, '-z-', b.broj_telefona, ']' )
SEPARATOR "-x-" ) AS telefoni, GROUP_CONCAT( CONCAT( '[', lj.naziv, '-z-', j.nivo, ']' )
SEPARATOR "-x-" ) AS jezik
FROM clanovi AS c
LEFT JOIN broj_telefona AS b ON c.id = b.clanovi_id
LEFT JOIN clanovi_jezik AS j ON c.id = j.clanovi_id
LEFT JOIN lista_jezika AS lj ON j.lista_jezika_id = lj.id
WHERE c.deleted = 'N'
GROUP BY c.id
ORDER BY c.prezime DESC
LIMIT 0 , 30

evo i rezultata
=====================================
6 Šuštić Nikodije [kućni-z-8240679]-x-[mobilni-z-+382-62-5554443]-x-[poslovni-z-77777777]-x-[mobilni-z-065-888888888]-x-[kućni-z-444444] [španski-z-osnovni]-x-[španski-z-osnovni]-x-[španski-z-osnovni]-x-[španski-z-osnovni]-x-[španski-z-osnovni]
------------------------------------------------
5 Petrović Pera [mobilni-z-0655542526]-x-[kućni-z-018-756721]-x-[poslovni-z-018-200562] NULL
------------------------------------------------
20 Nikolić2 Jelena [kućni-z-4230679]-x-[mobilni-z-091-555555555]-x-[poslovni-z-11223344]-x-[mobilni-z-069-3459589] NULL
------------------------------------------------
19 Nikolić Nikola [kućni-z-011-5265356]-x-[kućni-z-011-5265356]-x-[kućni-z-011-5265356]-x-[poslovni-z-023-44444444]-x-[poslovni-z-023-44444444]-x-[poslovni-z-023-44444444]-x-[mobilni-z-061 [engleski-z-viši]-x-[ruski-z-srednji]-x-[nemački-z-osnovni]-x-[engleski-z-viši]-x-[ruski-z-srednji]-x-[nemački-z-osnovni]-x-[engleski-z-viši]-x-[ruski-z-srednji]-x-[nemački-
------------------------------------------------
8 Mitić Mića NULL [nemački-z-osnovni]-x-[arapski-z-viši]
=====================================

Sto se mene tice, ja cu se tih duplikata resiti preko php-a (array_unique), ali bih voleo da znam da li sam pogresio u dizjanu i normalizivanju podataka, i da li postoji neki nacin da dobijam bez dupliranja podataka

Verovatno moze preko kursora, ali njih nikada nisam koristio.

Hvala unapred!

Damping baze

Code:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 06, 2010 at 08:34 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `test_gc`
--

-- --------------------------------------------------------

--
-- Table structure for table `broj_telefona`
--

CREATE TABLE IF NOT EXISTS `broj_telefona` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clanovi_id` int(10) unsigned NOT NULL,
  `tip` enum('kućni','poslovni','mobilni','inostrani') NOT NULL DEFAULT 'mobilni',
  `broj_telefona` varchar(50) NOT NULL,
  `komentar` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `clanovi_id` (`clanovi_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34 ;

--
-- Dumping data for table `broj_telefona`
--

INSERT INTO `broj_telefona` (`id`, `clanovi_id`, `tip`, `broj_telefona`, `komentar`) VALUES
(11, 5, 'mobilni', '0655542526', 'moj mob koji ne radi'),
(12, 5, 'kućni', '018-756721', 'drugi telefon'),
(13, 5, 'poslovni', '018-200562', 'treći telefon'),
(15, 20, 'kućni', '4230679', ''),
(16, 20, 'mobilni', '091-555555555', ''),
(17, 20, 'poslovni', '11223344', ''),
(18, 20, 'mobilni', '069-3459589', ''),
(19, 6, 'kućni', '8240679', ''),
(20, 6, 'mobilni', '+382-62-5554443', ''),
(21, 6, 'poslovni', '77777777', ''),
(22, 6, 'mobilni', '065-888888888', ''),
(23, 6, 'kućni', '444444', ''),
(24, 19, 'kućni', '011-5265356', ''),
(25, 19, 'poslovni', '023-44444444', ''),
(26, 19, 'mobilni', '061-99999999', ''),
(27, 19, 'mobilni', '063/5689898', ''),
(29, 19, 'kućni', '011-5265356', ''),
(30, 19, 'poslovni', '023-44444444', ''),
(31, 19, 'mobilni', '061-99999999', ''),
(32, 19, 'mobilni', '063/5689898', ''),
(33, 19, 'mobilni', '064-55-88-999', '');

-- --------------------------------------------------------

--
-- Table structure for table `clanovi`
--

CREATE TABLE IF NOT EXISTS `clanovi` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ime` varchar(250) NOT NULL,
  `prezime` varchar(250) NOT NULL,
  `deleted` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`),
  KEY `ime` (`ime`,`prezime`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `clanovi`
--

INSERT INTO `clanovi` (`id`, `ime`, `prezime`, `deleted`) VALUES
(5, 'Pera', 'Petrović', 'N'),
(6, 'Nikodije', 'Šuštić', 'N'),
(8, 'Mića', 'Mitić', 'N'),
(19, 'Nikola', 'Nikolić', 'N'),
(20, 'Jelena', 'Nikolić2', 'N');

-- --------------------------------------------------------

--
-- Table structure for table `clanovi_jezik`
--

CREATE TABLE IF NOT EXISTS `clanovi_jezik` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clanovi_id` int(10) unsigned NOT NULL,
  `lista_jezika_id` tinyint(3) unsigned NOT NULL,
  `nivo` enum('osnovni','srednji','viši') NOT NULL DEFAULT 'osnovni',
  `komentar` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `clanovi_id` (`clanovi_id`,`lista_jezika_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `clanovi_jezik`
--

INSERT INTO `clanovi_jezik` (`id`, `clanovi_id`, `lista_jezika_id`, `nivo`, `komentar`) VALUES
(1, 8, 6, 'osnovni', 'valjda nema onog drugog jezika, tj prvog'),
(2, 8, 7, 'viši', 'ovo je treći po redu, ali prvi u srcu'),
(3, 6, 3, 'osnovni', 'valjda nema onog drugog jezika, tj prvog'),
(4, 19, 1, 'viši', ''),
(5, 19, 5, 'srednji', ''),
(6, 19, 6, 'osnovni', '');

-- --------------------------------------------------------

--
-- Table structure for table `lista_jezika`
--

CREATE TABLE IF NOT EXISTS `lista_jezika` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `naziv` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `lista_jezika`
--

INSERT INTO `lista_jezika` (`id`, `naziv`) VALUES
(1, 'engleski'),
(2, 'francuski'),
(3, 'španski'),
(4, 'italijanski'),
(5, 'ruski'),
(6, 'nemački'),
(7, 'arapski');

_{[Ovu poruku je menjao ngj dana 06.08.2010. u 23:35 GMT+1]}

Odgovor na temu