Need help with relationships in table (1 Viewer)

Rare87GT · Nov 25, 2004

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.

Mile-O · Nov 25, 2004

Rare87GT said:
Tell me if my relationships are correct and what I need to do.

Firstly, using ID as the name for a primary key is not a good practice. Especially when you are relating it to a field in a table with a completely different name.

It's best to use an Autonumber as the primary key but it seems, at times, as if you have used text (i.e. Name which, incidentally, is also a reserved word in Access and not advised) which, when indexing via queries, can slow down the database.

The common practice is to give your primary key the same name as the object the table represents.

i.e.

in a table called tblDepartments, the primary key would be DepartmentID.

And, when referring to the department in, for example, a table of employees called tblEmployees then it's best just to call your field DepartmentID

i.e.

tblEmployees - Tablename
EmployeeID - Autonumber
Forename - Text
Surname - Text
DepartmentID - Number

And then, in the relationships window I would just connect DepartmentID to DepartmentID.

At the moment I can't really determine much about your relationships when ID at one moment appears to be text but in another instance it's indeterminable from just looking. I can see that you have one-to-one relationships which certainly doesn't appear correct.

Also, with regards to normalisation, the Cars table can be broken down into further tables for Models, Engines, and Colours.

I don't know what to make of the Inventory table as the field names are plurals which makes me think that you have a repeating group of either:

checkboxes - where you tick off each of these components;
numeric fields - where you enter the quantity in stock;

Either of these methods is wrong and is a definite indication that you require a further table for products.

In the correspondence table you have fields called Name and Date. I've already commented on Name being a reserved word but so is Date. In fact, any word that you use either as a function name, an object type, a data type, or other keyword that Access, by default (or by adding further references), understands, is a reserved word.

Two other things to commment upon:

When naming tables it is best to use the common prefix tbl. i.e. tblDepartments. The reason for this is that there are a) you can easily recognise it as a table; and b) it stops you giving queries the same name (use the prefix qry for them ) so that Jet doesn't know what is being referred to - the table called Cars or the query called Cars.
[*]When naming anything in Access or VB(A) then it is best not to use spaces or special characters within their titles. The common practice is to gather the name up as one word and to propercase the title. Thus Date Shipped becomes DateShipped. Although Access allows the practice of spaces and special characters this alleviates later problems with coding SQL as referring to items with said negatives means remembering to surround them with square brackets.

Mile-O · Nov 25, 2004

In addition, you probably need a new table for Communication Method too.

Rare87GT · Nov 25, 2004

See Im kind of confused at this point. I got the idea of what I want done but I can't seem to put it together. I somewhat understand what you wrote but for the most part can't put it all together. Maybe if you had some sort of Instant message program I could send you what I have so far on the relationships. I just can't seem to get started, I have my ideas of exactly what I want to do just can't put it all together. Any way you could help me out. If not, could you explain like what relationships need to go where and what input data I need to change in my tables. I just wanted to get this going and am stuck. Any help you can to get the tables sorted, what line items I need in the tables and the relationships would be great so I can start making my forms and so forth. How bout lookups and validation tables. Thanks man I really appreciate your help.

Mile-O · Nov 25, 2004

Rare87GT said:
Maybe if you had some sort of Instant message program I could send you what I have so far on the relationships.

Not while I'm in work. I tend to keep all my contributions to the forum since that's what it is here for and don't respond to PMs asking for help either.

I have my ideas of exactly what I want to do just can't put it all together.

Firstly, you need to tell here what you ideas are - what is your database supposed to do. Knowing only that your database "is something that a turn key automotive performance shop would use" doesn't help me - or anyone else, I'd wager - know what its purpose is.

So, some questions:

Are your customers individuals or companies?
Can a customer have more than one contact address/telephone/email?
Can a customer have more than one car?

I can't see the purpose of the other three tables - Inventory, Correspondence, and Jobs at the moment. Can you describe the purposes of these and how you expect they should work with respect to the data held in other tables?

How about lookups and validation tables.

Lookups are just part of the overall table design. I have no idea what you mean by validation tables.

Once you've answered the questions above we can start to normalise your database to ensure more robust relationships and a good sound foundation on which to build the rest of the database.

Rare87GT · Nov 25, 2004

Customer - name, address, phone, and email.
Cars - VIN, model, year, color, engine, and notes. One to many link with Customer.
Jobs - Work done, date received, date shipped, price charged, paid status, and notes. One to one link to Customer, one to many link to Cars.
Correspondance - Date, name, communication method, reason, notes, and employee. One to many link to customer. One to one link with Jobs.
Employees - Name, address, SSN, salary, speciaties, phone, and title. One to many link with Correspondance.

For lookup tables, I would create a lookup table for all the possible jobs that could be done (engine swaps, body kits, repairs, etc). And maybe create another for the type of correspondance.

For validation, validate the phone numbers, SSN, and emails.

Instead of Employee, I was going to also do a table for Inventory. Engine swap kits, suspension and brake componants, consumables (oil, grease, hardware). The Job table could use the Inventory table to fill in Jobs. Instead of Correspondance, you could do a Job_Parts table. This would list all the parts (from Inventory) that were used for a Job.

Rare87GT · Nov 25, 2004

Maybe you could see what I should do from that standpoint w/relationships, so forth.

Mile-O · Nov 25, 2004

Rare87GT said:
Customer - name, address, phone, and email.

I can see that but is the customer an individual or a company? If it's an individual then they should split the Name (reserved word, remember

) field into Forename and Surname otherwise change it to CustomerName.

Cars - VIN, model, year, color, engine, and notes. One to many link with Customer.

You currently have it set as a one-to-one link with the customer meaning that you can currently only have one car per customer. The primary key of the customer should not join to the primary key of the cars table. The primary key should join to a foreign key in the cars table.

Jobs - Work done, date received, date shipped, price charged, paid status, and notes. One to one link to Customer, one to many link to Cars.

You are missing the fact that you may have multiple jobs on the same car and therefore have a many-to-many relationship that you need to simulate. What is the Work Done field supposed to denote? Actually, can you explain this table in detail as nothing within it makes sense to me. I don't know what a job has to do with shipping; sounds more like an orders table may be required.

Correspondance - Date, name, communication method, reason, notes, and employee. One to many link to customer. One to one link with Jobs.

But what's the purpose of this table? Is it for job enquiries/requests/etc.?

Employees - Name, address, SSN, salary, speciaties, phone, and title. One to many link with Correspondance

Specialities implies you have non-atomic data (i.e. "customer care, electronics") in this field. This needs to be normalised into a specialities table and then, since an employee can have many specialities and a speciality can apply to many employees then you need a further junction table to simulate the many-to-many relationship you actually have here.

For lookup tables, I would create a lookup table for all the possible jobs that could be done (engine swaps, body kits, repairs, etc).

If these jobs are set prices then include the price within this table too.

And maybe create another for the type of correspondance.

Good.

For validation, validate the phone numbers, SSN, and emails.

You can use code to do these when tied to forms - that's a bit in the distance just now. Don't worry about it for now.

Engine swap kits, suspension and brake componants, consumables (oil, grease, hardware).

Now we need to develop the inventory table into a proper catalog. Maybe we can create a inventory category table (i.e. seating, electronics, engines, etc.) and then a product table (tied to the category table) that lists the complete catalogue of products with their prices, suppliers, etc. You'd need a suppliers table too.

Once you've responded to my questions here I'll have a think about a suggested structure.

Also, does any of this make sense? Is it clarifying in any way where you have been going wrong?

Rare87GT · Nov 25, 2004

Well see here is my deal man. I am going to be straight forward with you on it. Usually when I do work and stuff for an assignment it is much easier if someone showed me and got me started and going in the direction so I can see where Im going wrong. I mean I somewhat understand what you are meaning in your helpful responses but I am just not totally sure I want to start working without knowing it all. Is there any way you can or anyone else setup and structure all my tables and relationships and maybe send me a copy back so I can get started and be off in at least the correct way? Is that too much to ask?

Mile-O · Nov 25, 2004

Rare87GT said:
Is there any way you can or anyone else setup and structure all my tables and relationships and maybe send me a copy back so I can get started and be off in at least the correct way?

I've already said that I'll give you a suggested structure - probably in an upload - but I can't do it until you answer the questions above specifically. Otherwise I'm just stumbling about in the dark drawing lines from one table to another too.

Rich · Nov 25, 2004

Far better to have a separate table recording invoice payments than yet another status field

Rare87GT · Nov 25, 2004

SJ McAbney said:
I've already said that I'll give you a suggested structure - probably in an upload - but I can't do it until you answer the questions above specifically. Otherwise I'm just stumbling about in the dark drawing lines from one table to another too.

Ok well how about this. Here is my idea and what I want to do in this. I want to create a database for a Custom Car Shop. Maybe you could define the tables and make the relationships in a .db file and send it to me and I can identify it and go from there rather than me totally redesign what I initially had and edit that. Would that work? I dont mean to be a burden which you probably hate about me on this place. But I just figured I had to ask and see what you guys could do for me. Would that work? You could email it to me after you get it structured in a way you think would work and I can verify it and start enterting data for all the fields and get moving a ton faster than I am now. Please let me know and if you can email it to me, thanks and let me know as soon as you can. btw I am working with access 2003.

Mile-O · Nov 25, 2004

Rare87GT said:
You could email it to me after you get it structured in a way you think would work and I can verify it and start enterting data for all the fields and get moving a ton faster than I am now.

For the second time, I can't structure anything until you answer my questions.

Rare87GT · Nov 25, 2004

For the customer field, it would just be for individuals only not company's.

For the jobs field, the data within it means:

For like work done I was meaning what type of work was done, engine swap, brake upgrade, suspension modifications.
Date received was meaning when the car was received at the shop.
Date shipped was meaning when the car was shipped back out or completed.
The price charged for the particular job.
Paid status, meaning if it has been paid or not.
And notes, any additonal notes that needed to be added, etc.

I was thinking, I could maybe even substitute the correspondence table with the Job Parts Table. This would list all the parts (from Inventory) that were used for a Job.

Is that clearing some things up. Let me know what else you need to get started structuring this form in a .db. Thanks.

Mile-O · Nov 25, 2004

Rare87GT said:
Is that clearing some things up.

It's much better. Makes things a lot clearer and opens up some redundancies.

Rare87GT · Nov 25, 2004

SJ McAbney said:
It's much better. Makes things a lot clearer and opens up some redundancies.

Do you happen to get on AIM or MSN or Yahoo messenger programs? Would you be able to design my structure for the database with the tables and the relationships involved? If you could that would be really awesome. Get with me on how you can do this for me, thanks.

Mile-O · Nov 25, 2004

From earlier in this thread:

Rare87GT said:
Maybe if you had some sort of Instant message program I could send you what I have so far on the relationships.

SJ McAbney said:
Not while I'm in work. I tend to keep all my contributions to the forum since that's what it is here for and don't respond to PMs asking for help either.

Mile-O · Nov 25, 2004

Here's my version. From the original 5 tables I've expanded it into 22 tables.

Any questions about any of the tables then just ask.

I'm not too sure about the invoicing/ordering side as that's something I've never built a database for and, subsequently, never looked into. If someone could look at the structure and make further suggestions I'd be grateful.

Rare87GT · Nov 25, 2004

Ok. I saw the tables you have. Dont you think having 22 tables is a bit much??? haha. Is there any way you could maybe make it a wee bit smaller like around 8-10? I was just curious cause now I have overload on tables, haha. Let me know.

Rare87GT · Nov 25, 2004

Can you make it a little more simple you think for me? I really appreciate what you are doing for me. I just dont need more than 6-8 tables I would say. Do you think you could simplify it? Thanks.

This is all I need, refer to this below.

Tables

1. There should be a minimum of FIVE significant tables. A significant table has more than two attributes/columns.
2. All tables will be normalized to at least third normal form to prevent update anomalies.
3. All attributes must have captions and descriptions in the table design form.
4. Use predefined input masks where appropriate e.g. phone numbers, social security numbers, postal codes, etc.
5. Include at least two user defined input masks that you create e.g. an input mask to insure the letters in a state code are all capitalized.
6. There will be at least two lookup tables. Lookups can be done using one ore more of the five significant tables or you can create a table just used for looking up values (e.g. State Code Lookup table) that will be entered in other tables in your database.
7. All primary key fields will be required entries.
8. Non-key fields containing the minimum essential data to make each record meaningful will be required entries.
9. All initial entry tables will have validation criteria included to insure the quality, accuracy, and correct format of the data.

Need help with relationships in table (1 Viewer)

Registered User.

Back once again...

Back once again...

Registered User.

Back once again...

Registered User.

Registered User.

Back once again...

Registered User.

Back once again...

Rich

Guest

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Back once again...

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread