Thread: db design

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    vancouver, bc
    Posts
    145
    Rep Power
    15

    Post db design


    Hi,

    I'm starting my first attempt at creating tables and stuff for my friend.

    it started out ok cause I got some help from my other friend but 'm kinda lost now.

    I want to use mysql..but I also want to use postgres and make use of other stuff like foreignkeys and stuff

    I also want to track which auto parts are compatible with which cars. (1 part can be used on many car models)

    i also dunno how keys work..(i haven't defined any primary keys, i dunno which should be and which shouldn't be)
    ---
    CREATE TABLE part_categories (
    category_id INT(3) NOT NULL AUTO_INCREMENT,
    category VARCHAR(30),
    );

    CREATE TABLE companies (
    company VARCHAR(30),
    contact_person(20),
    address VARCHAR(30),
    city VARCHAR(20),
    st_prov VARCHAR(10),
    postal_code VARCHAR(10),
    country VARCHAR(20),
    phone_number CHAR(14),
    phone_ext CHAR(4),
    email VARCHAR(30),
    note VARCHAR(512),
    keywords VARCHAR (128),
    big_logo BLOB,
    sm_logo TINYBLOB
    );

    CREATE TABLE parts (
    part_number CHAR(20),
    category,
    maker,
    name VARCHAR(30),
    description VARCHAR(1024),
    cost INT(5),
    price INT(5),
    stock INT(3),
    note VARCHAR(512),
    keywords VARCHAR (128),
    big_logo BLOB,
    sm_logo TINYBLOB
    );

    CREATE TABLE vehicle_types (
    id INT(3) NOT NULL AUTO_INCREMENT,
    maker VARCHAR(15),
    model CHAR(10),
    class CHAR(10),
    year INT (4),
    note VARCHAR(256),
    );

    CREATE TABLE purchase_items (
    invoice_number,
    part_number,
    quantity INT(4),
    unit_price INT(5),
    note VARCHAR(128)
    );

    CREATE TABLE parts_vehicle (
    part_number,
    vehicle_type_id
    );

    CREATE TABLE purchases (
    invoice_number,
    purchase_date DATE,
    customer_id
    );

    CREATE TABLE customers (
    ID INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    initials CHAR(3),
    address VARCHAR(30),
    city VARCHAR(20),
    st_prov VARCHAR(10),
    postal_code VARCHAR(10),
    country VARCHAR(20),
    home_phone_number CHAR(14),
    work_phone_number CHAR(14),
    mobile_phone_number CHAR(14),
    email VARCHAR(30),
    note VARCHAR(128)
    );

    ---
    Last edited by edwinx; October 23rd, 2002 at 12:06 AM.
    I know nothing

IMN logo majestic logo threadwatch logo seochat tools logo