Starting correctly?

[Ani]

Hello
Please help. I dont know quite what to do now.
I have created 5 tables
Tbl-CustomerDetails-Primary key autonumber
Tbl-HorseDetails -Primary key HorseID autonumber -with CustomerID as foreign key -Number
Tbl-PastureManagement - Primary/foreign key -HorseID - with CustomerID-number
Tbl-Invoice -Primary key -InvoiceId -autonumber with CustomerId as foreign key-Number
Tbl-Product -Primary key -text (mix of letters & no's) - foreign key CustomerID-number
They are related as so
CustomerDetails CustId 1---->many CustID in HorseDetails
HorseDetails HorseID1---->many CustID in PastureManagement
CustomerDetails CustID 1------>many CustID in Product
CustomerDetails CustID 1------>many CustID in Invoice

The customer will have more than one horse details record
Each Horse will have more than one Pasture management record
The customer may have more than one product record
The customer will have more than one invoice record

I need to record individual customers with each of their horses records. Please advise if I have I set the tables up in a way that is correct for the db.

Also what I should do now, I know I need forms next but do not know how best to set forms up. The information in all five tables refers to one customer.
Thanks for any help.
Ani

 

[Beetle]

There are a couple of things about your table structure that, at first glance anyway, would seem to be incorrect.

1) tblPastureManagement

This is, presumably, related to the care and feeding of horses. CustomerID is likely not necessary here. The owner of the horse is an attribute of the horse, not an attribute of the management of that horse. The only FK you should need here is HorseID. Once you know that, you can determine any attribute of that horse (like CustomerID) by retrieving it from tblHorseDetails via the HorseID.

2) tblProducts

Again, I don't see a need for CustomerID here. When you sell a product, you are going to create an invoice. It is the invoice (not the products that are billed on that invoice) that has a direct relationship to the customer. The products themselves (or the Product ID's, to be more specific) are an attribute of the invoice.

Before you move on to creating forms, it is important to get the table structure correct. I know next to nothing about the nature of your business, so I can't say anything for sure, but, like I said, at first glance these things appear to be incorrect as they are currently structured.

 

[jdraw]

I concur 100% with Beetle. Table structure is key to a successful database.
Here are some video tutorials that could help you.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[Ani]

There are a couple of things about your table structure that, at first glance anyway, would seem to be incorrect.

Before you move on to creating forms, it is important to get the table structure correct. I know next to nothing about the nature of your business, so I can't say anything for sure, but, like I said, at first glance these things appear to be incorrect as they are currently structured.

Thank You. Those points make sense. I run a very small service examining horse poo for worm eggs. The pasture management table is very important, it allows me to advise the customer the best way to manage any worm problems the horse may/may not have.
II will now look at some more tutorials, though to be honest it gets very confusing as it seems there are many ways to do things and depends on which version of access is being talked about. Im using 2010.
I will change the tables as suggested.
Now for the next step! eeeek!
Many Thanks
Ani
OOOh now Im not sure about the relationships...dont they have to be 1 to many to a foreign key, if I take the foreign keys out how do the relationships work?

 

[jdraw]

Get your tables structured before you do too much custom programming.

The table structures should not be dependent on the version of Access. In fact proper tables could be used by any DBMS.

 

[Ani]

Get your tables structured before you do too much custom programming.

The table structures should not be dependent on the version of Access. In fact proper tables could be used by any DBMS.

Yes I can see the Table structure is the same throughout. When you start looking at creating other things there seem to be differences in the ways that it is done. Some of it I understand! :-0
Cheers
Ani

 

[Beetle]

OOOh now Im not sure about the relationships...dont they have to be 1 to many to a foreign key, if I take the foreign keys out how do the relationships work?

The (CustomerID) fields that I recommended removing should not have been part of the relationship to begin with (in the case of those two particular tables), so removing them should not affect your relationships. I would envision your tables looking something like this (just a rough example of course);

tblCustomers
**********
CustomerID (PK)
FirstName
LastName
Address
City
State
ZipCode
Phone
* etc. for other attributes of each Customer

tblHorses
*******
HorseID (PK)
CustomerID (FK to tblCustomers)
HorseName
* other attributes of each Horse

tblPastureManagement
*****************
ID (PK)
HorseID (FK to tblHorses)
* other attributes of this entity

tblInvoices
********
InvoiceID (PK)
CustomerID (FK to tblCustomers)
InvoiceDate
* other attributes of each invoice (not including line item details - those go in a different table)

tblInvoiceDetails
************
DetailID (PK)
InvoiceID (FK to tblInvoices)
ProductID (FK to tblProducts)
ProductQty
SalePrice
* any other attributes that may pertain specifically to the products that were sold on a given invoice.

tblProducts
********
ProductID (PK)
Description
ProductCost
* other attributes of each Product

So your relationships are (1 -------> M);

Customers ------> Horses
Horses ----------> Pasture Management
Customers ------> Invoices
Invoices --------> Invoice Details
Products --------> Invoice Details

Here you actually have a many-to-many relationship between Invoices and Products (an Invoice can have many Products, and a given Product can be sold on many different Invoices). In this case, tblInvoiceDetails acts as the junction table between the two

 

[Ani]

The (CustomerID) fields that I recommended removing should not have been part of the relationship to begin with (in the case of those two particular tables), so removing them should not affect your relationships. I would envision your tables looking something like this (just a rough example of course);



Here you actually have a many-to-many relationship between Invoices and Products (an Invoice can have many Products, and a given Product can be sold on many different Invoices). In this case, tblInvoiceDetails acts as the junction table between the two

Oh WOW! That makes so much more sense! Thank you. I can see what the junction tutorials meant now, I got so confused with that!
Brilliant...that must have taken some time to type out, Im really grateful!
Ani xx

 

[Beetle]

You're welcome. Happy to help.

 

[Ani]

tblInvoices
********
InvoiceID (PK)
CustomerID (FK to tblCustomers)
InvoiceDate
* other attributes of each invoice (not including line item details - those go in a different table)

tblInvoiceDetails
************
DetailID (PK)
InvoiceID (FK to tblInvoices)
ProductID (FK to tblProducts)
ProductQty
SalePrice
* any other attributes that may pertain specifically to the products that were sold on a given invoice.

tblProducts
********
ProductID (PK)
Description
ProductCost
* other attributes of each Product

So your relationships are (1 -------> M);

Customers ------> Horses
Horses ----------> Pasture Management
Customers ------> Invoices
Invoices --------> Invoice Details
Products --------> Invoice Details

Here you actually have a many-to-many relationship between Invoices and Products (an Invoice can have many Products, and a given Product can be sold on many different Invoices). In this case, tblInvoiceDetails acts as the junction table between the two[/QUOTE]

I think I have done this correctly, although I have changed some of the content of the Invoicetbl to include fields from the producttbl

They now appear so
tblInvoice
InvoiceID autono PK
CustomerID no. FK
ProductName *also in ProducTtbl
ProductPrice *also in ProductTbl
DateSampleProcessed *also in ProductTbl
InvoiceDate
InvoiceValue
PaymentType
DatePaymentReceived
DateDeposited
DateCleared



tblProducts
ProductCodeID -text -PK
ProductName * in tblInvoice
ProductPrice * in tblInvoice
DateRequested
DatePosted
DateSampleRecieved
DateSampleProcessed * in tblInvoice

tblInvoiceDetailsjctn
InvoiceDetailsjctnID -auto no. -PK
InvoiceID-no. -FK
ProductCodeID -text -FK (its text in the original tbl)
ProductName
ProductPrice
DateSampleProcessed

Are these tables correct?

Should the tblInvoice have the three same fields included as in the tblProduct? I have it fixed in my head that data/fields should not be repeated?

Should the tblInvoiceDetailsjctn have any of the other fields included from the tblInvoice or tblProduct?

I seem to remember watching a tutorial which said the junction table needed common fields from each of the other tables. Im a little confused as to having common fields and not repeating data?
I understand the fields in the junction table enable the many-many link.

Many Thanks
Ani xx

 

[Beetle]

Looking at your tables one at a time, I have highlighted (in red) the fields that don't belong or are at least questionable;

tblInvoice
InvoiceID autono PK
CustomerID no. FK
ProductName *also in ProducTtbl
ProductPrice *also in ProductTbl
DateSampleProcessed *also in ProductTbl
InvoiceDate
InvoiceValue
PaymentType
DatePaymentReceived
DateDeposited
DateCleared

ProductName, ProductPrice, DateSampleProcessed or any other field from tblProducts do not belong here. Remember, this table just holds basic information about each invoice (Invoice Number, Invoice Date, etc.). Any data about the actual Products that were sold on the invoice should be in tblInvoiceDetails.

InvoiceValue (by which I assume you mean the total Invoice amount) is a calculated value. It does not belong here, or in any table at all. Calculated values like this are not stored, they are just calculated on the fly as needed based on the underlying data. This is done in a query, a from or a report, depending on the particular circumstances.

tblProducts
ProductCodeID -text -PK
ProductName
ProductPrice
DateRequested
DatePosted
DateSampleRecieved
DateSampleProcessed

I don't necessarily see anything wrong with this table, although I do wonder about the last two fields. Is a Sample the same thing as a Product?

tblInvoiceDetailsjctn
InvoiceDetailsjctnID -auto no. -PK
InvoiceID-no. -FK
ProductCodeID -text -FK (its text in the original tbl)
ProductName
ProductPrice
DateSampleProcessed

Again, ProductName and DateSampleProcessed don't belong here but ProductPrice does (as long as we're talking about the price the product was sold at for the given invoice). The price of a Product is an attribute of the Product, but the price a Product was sold at on a given Invoice is an attribute of the Invoice (or the InvoiceDetails in this case). The price of the Product can change over time, but the price that Product "X" was sold at on Invoice "1234" must never change (lest your receivables be out of balance with your deposits when the tax man cometh). This is one of the exceptions to the general rule that you don't store the same data in two different tables.

 

[Ani]

Hi
OK, Thank you. I have definitely got confused with the junction table rules and overcompensated.
The three in red I added after you explained I needed the junction table so will remove those.
Yes Invoice Value will be total of that invoice. I will have to work out how to add that to a query when I get that far!

Product and sample are two different entities. Product is what I send to the client (packaging, instructions etc). Sample is the actual faeces sample that is sent back to me to be analysed. Oh I think that means I should have a different table? The sample is the result of the product being sent out?

I really appreciate the explanations of what an attribute of an item is etc, Im trying to think in those sorts of logical terms. I am a biologist, trained in scientific methods, Im not sure why this seems so difficult!

Many Thanks...I hope this is not too dreary for you...I really want to get this done correctly and Im afraid Im a bit of a perfectionist but if you dont aim high then you dont achieve your best!
Ani xx

 

[Ani]

Looking at your tables one at a time,



tblProducts
ProductCodeID -text -PK
ProductName
ProductPrice
DateRequested
DatePosted
DateSampleRecieved
DateSampleProcessed

I don't necessarily see anything wrong with this table, although I do wonder about the last two fields. Is a Sample the same thing as a Product?

Ok, I have just looked at all my tables again. I think I need another table with Sample details fields. I do not have anywhere to record the sample processing, analysis or results at the moment! Seems ridiculous that I could have missed something so obvious!
Your question asking if a sample is the same as a product is most pertinent!

Need to work this bit out now!
Thank you!
Ani

 

[Beetle]

Based on what you've said it sounds like you need a Samples table, which would have a relationship with tblHorses (so you know which horse the sample came from). If you perform different types of tests on each sample, then you will likely need another table (tblSampleTests perhaps) to record the details of each different test on a given sample (assuming that each different type of test has a similar set of fields). This table would need a SampleID field as a FK to tblSamples. If you do perfoem different types of tests, but each type of test has a completely different set of attributes, then it gets a bit more complicated because you may need multiple child tables for the Samples table.

 

[Ani]

Thats hugely helpful, again. Thank You.
At present I do three tests on each sample but each test is the same, the tests are repeated for scientific accuracy. So I will only need one table, I think. There are three repeated tests, each test has 3 numerical results. So the 9 numerical results are then calculated as an average. The same rule will apply for this as for the Invoice total value, Im guessing, in that it should be calculated 'on the fly' in a form, query or report?
Many Thanks
Ani xx