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

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Conceptualization Failure


    hmmm...

    I'm trying to wade through some tutorials (which seem to be rehash of 1NF, 2NF, etc.) to find and understand a concept. Not being a dev, I'll explain with some simple items and hope someone understands my rambling and can direct me to a place with more info.

    So, I've got three tables in the same database:

    Customer
    cust_id <- primary key
    name_l
    name_f
    address

    Equipment
    eq_id <- primary key
    make
    model
    serial_no <-- note that this is NOT primary key for a variety of reasons. SN may be missing, etc. so it is invalid for such use.

    Repair
    service_id <- primary key
    date
    work_performed
    billing_stuff

    I've worked through the 1-3NF and feel comfortable that I've reduced sets to lowest common denominator.

    Here's where I'm 'confused' -- how do you tie the pieces together? When I want to look at customers, I want to see the equipment they own, and the service history

    When I look at equipment, I need to know who owns it and if I've worked on it before.

    I've been trying to work through 'links' and that makes sense, but there's a fundamental flaw for me -- I create a record for a client, I need to remember what the client_id is and put it into the next table, etc. - there are about 10 related tables, and trying to remember what the ID is from one end to the other is a problem -- as is the concept that one client could have 10 pieces of equipment -- and may have had multiple repairs on each one, throwing the numbering scheme out of shape quickly.

    I guess the part I don't understand is how you tie the different id's together to make a complete record that makes sense in a query.

    What am I missing from understanding this puzzle?

    Thanks much in advance.
    Last edited by drbob001; January 5th, 2013 at 06:19 PM. Reason: A couple of small repairs
  2. #2
  3. No Profile Picture
    Still Learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Montreal, Canada
    Posts
    55
    Rep Power
    39
    You have 3 isolated tables. You need to create tables to hold the relationships you mentioned. Say the list of hardware someone owns. You would need and owner ship id and two foreign keys, the usersid and the hardwareid. You coul select from this table the user a list all his hard ware.

    Your repair record could have on extra field a foreign key to the hardwareid being worked on.

    You have to find better tutorials. You need to understand the fundamental concept of a database is not just the lists of records but the how the relationships in the real world are implements with SQL.
    Last edited by admiraln; January 5th, 2013 at 09:51 PM. Reason: More thoughts
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    to expound a little on what admiraln said, you want 2 or 3 more tables such as :
    customer2equipment
    c2eid <-optional
    customer_id
    equipment_id

    equipment2service
    e2sid <-optional
    equipment_id
    service_id
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Thank you


    I'll do some more research to find a more complete tutorial set on the subject.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by bobert123

    c2eid <-optional

    e2sid <-optional
    in my opinion these are not "optional" at all -- they are redundant, unnecessary, and inefficient overhead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    i tend to agree.
    mostly, i think people add them in just to avoid the "warning: no primary key", which is a really bad reason.

    but those tables sometimes grow and are used to store further information. such as the table instead being a sales table showing the customer_id, the equipment_id and details of the sale. in that case, you do want a primary key
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by bobert123
    in that case, you do want a primary key
    Code:
    CREATE TABLE customer_equipment
    ( customer_id INTEGER NOT NULL
    , equipment_id INTEGER NOT NULL
    , PRIMARY KEY ( customer_id , equipment_id )
    );
    vwalah

    Comments on this post

    • Jyncka agrees : Yay for composite primary keys!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo