Unanswered: Can someone check my relationships for me
Here are my relationships for my 5 tables I created. My database is something that a turn key automotive performance shop would use. Tell me if my relationships are correct and what I need to do. I think I got a good start but I am not sure on a few things. Also what do I need to do for my validation and look up tables. Any help on that would be great. Thanks.
I suspect you need to go back to basics
Each table should have a unique column (which can be either a real world value or a unique value within the database)
You genereate a RI link when you have the same item in two tables with identical values. The link can be one to one, one to many, or many to many. In most RI databases you will usually only use one to many's - think of it as a parent to child relationship one parent to many children
It is arguable that for Cars this could be the VIN - I'm not sure when the world switched to globally unique identifiers for VIN's but it might be practical to create a column CarID (not ID) which is an autonumber primary key
For Customers you will need to generate a unique index, I would suggest CustID
It isn't smart to have the same column name in a database used in several tables UNLESS its the same value. Calling it ID doesn't cut it.
I don't think you have your table Inventory at all right, it looks like you are trying to define the data not the data specification. Normally in an inventory I would expect to see something like PartID (internal ref), ExtpartNo, PartDesc, Qty, Price etc.....
To associate a car to a customer in one of the tables you need a column to hold the 'child' field. For example if you wanted to set this up you might expect a customer to have several cars (after all the car is owned by the customer, not the other way round), so you would expect to define a column CustID in Cars, which is a long, required column
you would then link form CustID in Customers to CustID in Cars. See why we suggest you use the same name for the same fields. This will help when you come to define queries,reports & forms as the field names will be the same and the userinterface will automatically select the correct linking fields.
I'm not convinced that the table jobs is linked correctly. I would expect to see in this table CustID & CarID to link to Customers and Cars.
How have you deifned 'communication method', you can use a lookup which may work OK, but only if you can encapsulate every possible method of communication. I would suggest a separate table with its own unique index
I think most garages communication method is probably grunting rather than phone, fax, email, conversation but maybe its different in yoiur neck of the woods.
How have you defined your RI link between Cars and Customers
Bear in mind a customer can have several cars
The same car may appear in the body shop over time owned by different owners. Is it one to one, one to many, is it linked or do you want to use an RI link.
Why haven't you defined a table for employee
why haven't you identified who did the work, might be nice to know that gorillaa "X" worked on customer "Y"'s car(s)
A final point on chosing column names Please don't use spaces in names its nasty, don't use under scores "_" if you change to a different sql vendor you have to redesign things don't use reserved words like "date" its messy.
so a few suggestions
in table correspondance
define column "date" as "corspdate" or something similar (dont use cdate 'cos thats a vb/vba function)
define column Communication Method to be CommunicationMethod or CommMethod, or even CommID if you are using a separate table
That shld give a few pointers
good luck with the homework
I sent you a pm. Im just heavily confused at this point, need some guidance or Im gonna get real lost, let me know man.
Might I highly suggest that you seriously find a great book on Relational Databases. Any book on Access would be great, and either get one for those who don't know anything about databases, or get one that's a bit higher level and it should give you a brief overview in the early pages of database basics. Seems you are trying to do something totally new to you and not have the ground work done as of yet. Not being critical but trying to be helpful. Otherwise if not already (by fashion status), you will soon become bald. Pulling your hair out trying to understand how to do it. Just the same with most things, before taking on a task, try to get some basic understanding of it first.
From what Healdem is saying reflects that you haven't gotten the Foundation set yet. Haven't made it to first base. There are many good books out that tell all about creating databases in libraries and even online.
Those are a few sites that may give you some insight as to how to get started. If you have taken on the task of designing a database, but have no real concept of how one is put together, I feel for you. Do yourself a favor. After you read some of the suggested sites or any book you can get your hand on, FIRST, and that is essential FIRST...get all the pieces you will need on PAPER. Coming to the computer to create the database without your blueprint..(paper design) will do a lot to confuse you. Not saying it can't be done but all the pros suggest doing it on paper first. You'll be amazed to find out how much you are still missing. Better to use an eraser on paper than keep recreating a database on the computer. Once you start reading, you'll soon get the feel for what to do from then on, thereby avoiding anymore headaches or royal pains. And truly I hope you take this as very friendly advice, as I myself had to learn it this way. THEN I came here and other sites for helps once I hit a bad spot in my development.
wish you well and have a nice one,
hats off to you healdem for taking the time to check out his db and give him some pointers too. That's what this is all about. Laterz......