Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: simple database modelling

    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

  2. #2
    Join Date
    May 2008
    Posts
    277
    Use a foreign key:

    Code:
    CREATE TABLE cars (
        ...
        color_id integer references colors (color_id),
        ...
    );
    PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE TABLE

  3. #3
    Join Date
    Apr 2011
    Posts
    19
    Quote Originally Posted by igor88 View Post
    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 View Post
    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.
    Attached Thumbnails Attached Thumbnails 2011-06-07_162306.png   2011-06-07_162432.png  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •