table design and relationships...

[snowman]

hey.. I’m a complete newbie at designing tables and relationships, so pls bear with me..

Im trying to construct a db that records all the details for an importing company.

Background: the company imports goods (shipments conducted by Freight Company), from a Supplier, in Containers, and then arranges Delivery of the goods (delivery completed by Haulier Company). Customs requirements must also be met.

so far these are the tables I have:

tblShipping
tblContainer
tblDelivery
tblCustoms
tblSupplier
tblFreightComp
tblHaulierComp

Here are the relationships:
1) Each shipment (tblShipping) can have many Containers, but each container can only have one shipment (tblShipping) : 1:M
2) Each shipment (tblShipping) can only have one Supplier (tblSupplier), but each Supplier can have many shipments (tblShipping): 1:M
3) Each shipment (tblShipping) can only have one Freight Company (tblFreightComp), but each Freight Company can have many shipments (tblShipping): 1:M
4) Each shipment (tblShipping) can only have one Haulier (tblHaulier), but each Haulier can have many shipments (tblShipping): 1:M
5) Each Container can only have one Delivery (tblDelivery), but each delivery can have many containers: 1:M
6) Each Container can only have one Customs Check (tblCustoms), but each Customs Check can have many containers: 1:M
7) Each Delivery can only have one Haulier, but each Haulier can have many deliveries: 1: M

These are the most important relationships (not all of them).

Where I’m getting confused is whether or not I should (or need to) create additional junction tables? If someone could have a look at the attached screenshot (of my tables with fields and pk's - but no relationships defined yet) and advise, I’d really appreciate it!

Also, im having some major probs with my variables and forms… the P/C No in the Shipping Table is the most important value here. This number is used repeatedly throughout the process. Problem I’m having is that this number needs to be alphanumeric, and also get passed to each other table (hence P/C No as a fk in each other table). How can I do this? Ive been playing around with loads of diff relationships but with no luck. I ideally need this alphanumeric number, when entered in the first form, to be ‘sent’ to other forms, as I chose. How can I do this?

Any help is really appreciated!

Thanks

snowman

ps - sorry for such a long post!

 

[Robert88]

Checked the Microsoft Site?

Hi snowman,

Have you checked the Microsoft site?

There is an orders management database on the microsoft site, maybe that will give you some good ideas?

http://office.microsoft.com/en-us/templates/TC010185481033.aspx

Goo dLuck with it.

Robert88

 

[snowman]

Hi guys, thanks for the replies.

Robert88 - thanks for that link, im gonna have a look at that db now, and see if it can help me along..

Pat - thanks for the tips, ill sort out these normalization issues... thanks for the suggestions.. ill re-think the structure and let you guys know if i hit any more brick walls

akash

 

[snowman]

hey guys

just wondering if you could have another look at my new relationships.. i think this is a better attempt, can you guys have a look and see if theres anything obvious im missing please?

im still a bit confused about junction tables - my understanding is that they are required for M:M relationships... my db does not have any M:M relationships (well, that i can see!). but for this new attempt ive created a new 'Orders' table that i think is acting as the junction table - is this correct?

thanks

 

[snowman]

Pat, thanks very much for your advice. I'll re-think the structure along your suggestions and get back to you! (i realise that you dont need to help out this much - but i seriously appreciate it!)

 

[snowman]

Hey Pat,

you suggest merging the Shipping and Customs data into either Container or Order - i would have thought that having stand alone tables, that contain the info on these seperate entities was a better approach?

I would honestly prefer to keep these tables as individual tables... if you look at my latest attempt, could you direct me a little more with getting these tables properly linked together pls? i think im getting there, well i hope anyway!

you're right, tblShipping, tblContainer and tblCustoms should be linked together, and not all to tblOrders as I previously had.. i think these new relationships are correct. would it be best to now link the 'BLNo' field in tblCustoms (PK) to tblOrders as the FK ? then link tblOrder to tblDelivery with the OrderID field?

thanks for your help and advice

 

[snowman]

1) No - each container has one customs record. its more the case that each shipment (with how many containers) will have one customs record. hmmm i think maybe this relationship incorrect on this basis? would your suggestion of merging tblShipping and tblCustoms be advisable?

2) No - each container will only generate one Shipping Record

3) No - partial delivery will not occur. Each Container will be delivered completely

also, im not sure if this will make much of a difference, but once these tables are properly designed, the main form that i create (the Shipping Record) will need to call on information from nearly all of these tables ... this is why i want these tables to stand alone - otherwise i would be making a db that is not much better than a flat file system (if all the tables end up being merged)... will this be a problem - calling info from all tables? At the moment, none of the test forms im creating are working correctly - thats why im hoping its the relationships i have incorrect.

 

[snowman]

Pat, you are really helping me along here, and I seriously appreciate it. I can see how my current structure is still weak. Im in the process of rethinking these table designs. It seems to me, given all your advice thus far that I have over complicated this importing-shipping-delivery database…

2) Can there be more than one container for one shipping record – yes there can be. How should I remodel this relationship?

As for tblOrder, the reason I included it was so as to have a method that calls on all of the important unique information and ID’s in this db. But as you have pointed out, I don’t think that its necessary either.

The Shipping record is the most important data entry form that this db needs to address. From there, there are various other data entry forms that must be completed, but all of which will call on information from this Shipping record form

As for merging tblDelivery with another table, based on these new relationships, do you believe that it still should be merged? As you can no doubt see, im no database programmer, well not yet (!) so im not really sure where this table could be merged? Could it not be linked up with tblContainer as per the ContainerID field? Or is there a better way to do this?

A question I have for you based on this new design, how can I link tblSupplier with tblShipping? Because technically, the supplier only supplies the goods, and are not responsible for the shipping process. What if created a new table called tblGoods, that would incorporate the diff fields there are for the goods, which then could be passed onto tblShipping?

Thanks a million for all this mate
akash