table with multiple entities

[anishkgt]

Hi,

A little confused about the entities in the table.

In my table tblDelivery i can several repeating entities from the table tblAgencies. One agency has several deliveries. So is it ok to see several agencies(entities) in the table tblDelivery or is it against normalization. Should i be creating a junction table for this if that is the solution to it.


Thanks in advance.

 

[CJ_London]

So is it ok to see several agencies(entities) in the table

you should be seeing an agencyFK which links back to an agencyPK in an agency table where you have data such as agency name, address etc

 

[Grumm]

If the name is unique, he can use that as PK in the agency table.
(see more : https://en.wikipedia.org/wiki/Second_normal_form)

As far as i can see, you don't need a junction table if you have a correct foreign key and primary key.

 

[anishkgt]

Yes i do have it designed as said. but my concern is if its ok to see repeating entities of the of the foreign key from tblAgencies in tblDelivery ? Like there are numerous deliveries for one agency and that would create repeating foreign keys from the table tblAgencies in tblDelivery. Should a junction table be used if that is what is required ?

 

[Grumm]

That is normal behavior for foreign keys.
You only need a junction table of there was a many to many relation.

 

[CJ_London]

If the name is unique, he can use that as PK in the agency table.

really, really don't recommend that - what if the agency changes its name? you have all the FK's to update

 

[anishkgt]

really, really don't recommend that - what if the agency changes its name? you have all the FK's to update

Yes the agency name is unique as asiad above the update would fail if/when needed. I use the PK to link to the delivery table.

ok so hat clears my doubts. Could you give me a many to many to relationship with respect to what i have. I understand the idea of many to many relationship but a little more clarity would help.

 

[Grumm]

The example on wikipedia uses "Manufacturer" as PK and FK...
Maybe not the best one ever... Sorry

 

[CJ_London]

didn't say you can't, just don't recommend it

many code examples use names for simplicity to demonstrate a principle

 

[CJ_London]

Could you give me a many to many to relationship with respect to what i have

an invoice line record typically provides a many to many relationship between an invoice header and product

one invoice header has many products

one product has many invoices

 

[anishkgt]

one invoice header has many products

In my database that would be One agency would have several deliveries

one product has many invoices

Can't figure how this relates in my database. Would it be like a reports table with several deliveries ?

 

[jdraw]

There are several free data models at
http://www.databaseanswers.org/data_models/index_all_models.htm

some with deliveries, some with products , and invoices.

 

[Grumm]

My big guess is that you have multiple deliveries for one agency. But you only have one agency for each delivery.

if that is the case, then you don't need a junction table to do this.

 

[anishkgt]

My big guess is that you have multiple deliveries for one agency. But you only have one agency for each delivery.

yes that is correct. Only one delivery at a time. So when would i need a many to many relationship or in other words a junction table

 

[anishkgt]

I am guessing, if i have a table delivery report then i would need a Junction Table. Where this table would have DeliveryReportID,DelID_FK. So i have multiple inventory to agencies and want the delivery reports to be recorded in a table.

So what i need is one Delivery report for several Deliveries and one delivery will not have several reports. So how does the attributes and relationship go ?


Sorry if it sounds stupid

 

[The_Doc_Man]

A table can have one of three categories of relationship to another table.

A. one-to-one - meaning that one and only one record in table A ever can or will correspond to one and only one record in table B. The ONLY time I have ever seen this done is to make it easy to isolate parts of the table for security or logistics reasons. I.e. table A is the "public" information about something and, for security purposes, table B is a 1/1 relation to A but holds business confidential data that you wish to scrupulously protect more than normal.

B. A one-to-many or many-to-one relationship is often used where one business entity is not "atomic" i.e. it is divisible.

A common case of 1/many is an invoice that has many line-items. One customer visit resulted in one invoice representing the purchase of several items. This is called the parent/child relationship sometimes, where the invoice as a whole is the parent and the line items are the child entities.

A many/1 table relationship is often a definitional or translation situation, in which some field in your table gets translated by a code. Simple (and somewhate contrived) case would be where you use a 2-letter state abbreviation in your invoice table's shipping delivery address (assuming you deliver...) Many invoices will have the same state abbreviation, many/1 case.

C. The many/many case is trickier. If you have a list of several products and at least some of those products can be obtained from more than one manufacturer, you have the makings of a junction table to elaborate the many/many relationship - because Access does not do many/many as a "native" relationship type. You can have many products coming from the same vendor and can have many vendors who supply the same product. There is your many/many case.

D. The fourth type of table is not a separate case but often requires further explanation. One of the possible numbers among "many" is zero. Therefore, it is possible to define a table that sometimes has NO child entries for some parent records. This is just a many/1 or 1/many table that happens to have the chance that there is no extant link. An example of this is a many/many table between products and vendors where there was only one vendor for a particular product and that vendor just went out of business. You have the product but no place to get more of it. OR the vendor discontinued the product line, so the vendor is still valid but the link to a specific product is NOT valid.

Another example of this is if you use pre-printed invoice pads with numbers on the top of the page but at some point someone hoses up the order and has to start over, and that type of pad is such that you just have to tear off the page and void it. IF your business rules are such that you account for all invoices in a pad, then that voided invoice will have NO LINE ITEMS. I.e. a childless parent, a 1/0 case in an otherwise 1/many relationship.

I hope these examples help you to better understand relationship types.