January 5th, 2013, 05:59 PM
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:
cust_id <- primary key
eq_id <- primary key
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.
service_id <- primary key
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
January 5th, 2013, 09:37 PM
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
January 6th, 2013, 03:24 AM
to expound a little on what admiraln said, you want 2 or 3 more tables such as :
January 6th, 2013, 04:55 AM
I'll do some more research to find a more complete tutorial set on the subject.
January 6th, 2013, 05:23 AM
in my opinion these are not "optional" at all -- they are redundant, unnecessary, and inefficient overhead
Originally Posted by bobert123
January 6th, 2013, 07:58 AM
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
January 6th, 2013, 08:30 AM
Originally Posted by bobert123
CREATE TABLE customer_equipment
( customer_id INTEGER NOT NULL
, equipment_id INTEGER NOT NULL
, PRIMARY KEY ( customer_id , equipment_id )
Comments on this post