Might someone help with relating some Tables?

[djclntn]

For a Project I have this “mock” database of a used car dealership – ALL of the data is fictional.



Attached is an Excel Worksheet listing all of my Tables & field names in each of my Tables w/ PK & FK, but I don’t know/can’t figure out how to relate these Tables. Within this attached Worksheet I listed some primary queries I wish to run for starters. Also, I gave a few facts to some to the data.

Any help would be appreciated!

[orange]

DjcIntn, I posted a list of free videos on Normalization and E-R diagrams in
the last post at
https://www.accessforums.net/forms/i...orm-21571.html

I gave this list when you said you don't learn by reading; I think then you must learn by watching/listening etc. So hope you enjoy the videos.

Good luck.

[djclntn]

So, not to be disrespectful, but no one will look at my dbs? I’m sensing that it’s either sink or swim? I don't mean nor want to be nasty here; I’ve received lots of help here. Obviously even with watching the videos countless times I’m still struggling. I’ll go back & watch the videos AGAIN & see if something clicks.

Please, I’m NOT meaning to be mart with you just frustrated!

[RayMilhon]

Take a look at spreadsheet 2 I've reworked your table design a little. Take a look at it. I'll try and answer any questions you have on why.

[djclntn]

Thanks, Let me make the changes in my dbs & I'll get back to you

[djclntn]

Ok, first, none of my Tables are related -- can you tell me how to relate them?

[djclntn]

Wait, Didn't I just delete all of my foreign keys which is the root of relating tables? I don't understand?????????

[RayMilhon]

Foreign Keys are used to relate 1 table to the primary key of another table. That's it. It is not meant to be reciprocal. In other words if you have a Primary Key in table 1 and a foreign key in table 1 that points to the primary key of table 2 there is no reason and is more often than not a mistake to put a foreign key in table 2 to the primary key of table 1.

I'll explain using your tables.

First your sales rep table. You want 1 record for each sales rep. If you include the foreign keys Customerid and vehicle id then you have 1 record for each vehicle sold and each customer. So let's say a sales rep averages 5 cars per month you now have the same sales rep 60 times in a 12 month period. You don't want that.

vehicle Inventory-A Vehicle on your lot does not have a salesman or customer since it's on you lot for sale so why have a foreign key to customer and salesman. Include a sales table that has foreign keys to you vehicle inventory, salesman and customer.

Your Customer employer table should only have employer information. What happens if you have more than 1 customer at the same employer again you don't want to duplicate your data. So the foreign key for employer is in the customer table not the other way around.

Same for the Financial Institutions you want 1 record for each institution. I recommend you do a search for Database normalization and do some reading.

[djclntn]

I'm sorry to say but I feel as though you left me hanging here. Idon't know how to go aboutcreating any queries & what do I do with this Sales Table; what's its purpose?

[RayMilhon]

First the sales table is to track the actual sales.

You really need to understand normalization. There are sites all over the internet that do a much better job of explaining it than I could. If you truly want to learn to build Databases you need to do some reading and I'll be more than happy to do my best to answer any questions.

[June7]

Google: what is relational database

There will be dozens of responses to that query. Here is one http://computer.howstuffworks.com/question599.htm

The example in that link is flawed but good enough for now. It shows a People table with a field for City that contains a value that is the PK from Cities table. The Cities table could have more fields about the city (population, county, state). These other fields are more info about where the people live, but I don't want to repeat the data in the People table for every person in the same city (note 2 people in city code 3 - New York). If I want to know what that other info about the city is, I join the two tables in a query on the PK/FK City code fields and then all fields for the related records are available. This is a one-to-many relationship. Each person will have one city but each city will have many people.

A many-to-many relationship would require a third table - a junction table to associate data from two other tables.

Google: how to build query in Access
http://www.youtube.com/watch?v=epx0QOFoFj4
http://office.microsoft.com/en-us/ac...010291917.aspx

Google: how to create relationships in Access
Another YouTube http://www.youtube.com/watch?v=bBHewXpSgQQ

[June7]

I agree with most of Ray's revision. Relationships (PK/FK) are appropriate. The one variance I can take is the Sales table.

Let's assume:
1. every car will eventually sell to one customer
2. every car will be sold by one sales rep
3. every car will have one sell price
4. every car will have one sell date

With these assumptions, all of the fields for the Sales table could stay in the VehicleInventory table. If any of these assumptions fail (as in the possibility vehicle could be sold back to the dealer, chance of which is probably slim and could be ignored for this exercise), have to rethink setup.

A separate sales table makes most sense when the inventory is bought in bulk and then sold in single units, like canned goods. Can you imagine what a grocery store db must be like with hundreds of thousands of customer transactions a year? I mean, every pack of gum scanned through register creates a record in some table. Boggles my mind!

[djclntn]

Ray...

One thing puzzles me regarding the "Sales Table." Why am I doing double entering Data? When I enter the enterdata in the Customers, Sales Rep & Vehicles Inventory Table I also need to input the require data into the "Sales Table." Or, do I start with the "Sales Table" Table first & the data will appear in the accordance Tables?

[June7]

Is there some misspelling in post? Do you mean 'double entering Date'. Ray did not remove it but I think it is understood that with Sales table, DateSold will not be needed in VehicleInventory table.

What is 'accordance Tables'?

Did you read my previous post? You have options regarding Sales table.

[djclntn]

June,

It's 10 o'clock & I've been working on this since 9 this morning. Tomorrow I'll look at your proposal in combining the Sale Table with the VchlInventory Table.

Thank June. Ray.