simple database modelling

igor88 · #1 (**permalink**) 05-26-11, 05:09

hi all
i am new i databases and have some question

there are 3 tables in DB. idea is that one man can have MANY (so one to many relation) cars and car can have only one color

so a screen shot of data model from ACCESS where all is work fine

ImageShack® - Online Photo and Video Hosting

so...

PHP Code:


			
CREATE TABLE color

(

  color_id integer,

  color_name text,

   ,

  PRIMARY KEY (color_id)

);



CREATE TABLE cars

(

  id_record integer,

  car_model text,

  color_id integer,

  id_man integer,

  PRIMARY KEY (id_record)

);



CREATE TABLE main

(

  id_man integer,

  name text,

  surname serial,

  adress text,

  PRIMARY KEY (id_man)

);

and now how to correctly connect id_man in 2 tables and color id ? 1 one many in both cases.i am doing all in Open System Architect and it doesnt allow me do connect the field ...

thanks and sorry for my eng

futurity · #2 (**permalink**) 05-26-11, 19:53

Use a foreign key:

Code:

CREATE TABLE cars (
    ...
    color_id integer references colors (color_id),
    ...
);

PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE TABLE

donose.mihai · #3 (**permalink**) 06-07-11, 09:26

Quote:

Originally Posted by igor88

hi all
i am new i databases and have some question

there are 3 tables in DB. idea is that one man can have MANY (so one to many relation) cars and car can have only one color

so a screen shot of data model from ACCESS where all is work fine

ImageShack® - Online Photo and Video Hosting

so...

PHP Code:


			
CREATE TABLE color

(

  color_id integer,

  color_name text,

   ,

  PRIMARY KEY (color_id)

);



CREATE TABLE cars

(

  id_record integer,

  car_model text,

  color_id integer,

  id_man integer,

  PRIMARY KEY (id_record)

);



CREATE TABLE main

(

  id_man integer,

  name text,

  surname serial,

  adress text,

  PRIMARY KEY (id_man)

);

and now how to correctly connect id_man in 2 tables and color id ? 1 one many in both cases.i am doing all in Open System Architect and it doesnt allow me do connect the field ...

thanks and sorry for my eng

Quote:

Originally Posted by futurity

Use a foreign key:

Code:

CREATE TABLE cars (
    ...
    color_id integer references colors (color_id),
    ...
);

PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE TABLE

See attached images (it's easier to use a GUI Tool to create table, build queries and so on.

Internet Services

Web Development

Digital Marketing

Consumer Tech