Hi Folks

My Co Worker disagrees with my Suggested Schema for a Standard International Freight Shipping DB & has posted a simplified version of his own Schema on VBCity Forum

http://www.vbcity.com/forums/topic.asp?tid=44083

Would You chaps be so good as to take a quick look & tell them/me what U think of his aproach.

I think he's getting some shaky advise.

My proposed Schema is to use a Generic type Header that can cope with All of the different transport types with a TransportTypeCode Foreign key
(Actualy I've denormalized into a single Char cos there are only very few types of transport - Air/Sea/etc - but this is not the disagreement)

I Call it the FInvFopFip Aproach

Tables Are

FreightInvoiceHeader (FInv)
FreightOuterPack (FOP)
FreightInnerPack (FIP)
FreightInnerPackDetails

Freight Header table Fields

FreightInvoiceHeader(
FInvNo
FInvDate
FInvPickListNo
ModeOfTransCode
TransName
ETDDate
ETADate
DespatcherCode
ShipperCode
ConsigneeCode
DespAddCode
PortEmbarkCode
DelAddCode
PortDisEmbarkCode
DespContact
ShippContact
ConsigneeContact
FInvComments
)

Much Appreciated

GW

Hi Gwilly...

I went to the vbcity forum and took a look at your colleague's proposal, and read the whole thread that you go there.

I wasn't really sure what to suggest, on my first reaction I chose you approach, however the 3NF breakage bothered me a little, so I analized you friend's schema, but that didn't satisfied me either, since your point of troublesome reports and other programming issues held me back.

After some thought I'd say I'd go with your approach, at least that's the way I'd do it.

I must confess that I wasn't too happy with the idea of having to register on those forums just to take a look at the conversation, but I'm glad I did, the debate is very good and it truly shows the dilemmas that could come up on a db design.

Thx Zemog

My colleague & I actually took some enjoyment not only out of the subject matter of this discussion but in the oppurtunity to act like Adults about a difference of opinion.

So often I find that conceptual Logic can fall prey to personal desires of just wanting to win the argument at all costs.

Effectively this discussion ran over Budget & a decision had to be made.
My Colleague chose his Schema on the Basis of a recently imposed Business Rule that One Invoice Cannot Cover more than One Container (OuterPack) which effectively made it an outer Pack to Many invoices Scenario.
Many Invoice Header Details will be renamed to go against the Container (outer Pack)

The solution that he will provide will fit todays business requirement.
Unfortunately what tends to happen is that at some point someone will want to Freight something slightly different than hanging garments in a container using the same Freight System database.

Only when this happens do you find that you begin generating duplicate logicaly identical tables - then everthing after begins to turn complicated.

Meeting the Specification of requirements to the letter as my colleague is intent on doing is not neccesarily wrong but I feel less wise.

I cannot agree however with the naming conventions used.
This is the Core of the 3NF argument having different tables for different types of carriers because the carriers seem to have slightly different data requirements.

It's a Judgement call based on the persons familiarity with the Data & It was plain to see that he had not previously defined an argument (which did not revolve around being too specific with field naming choices) to split the SeaFreight away from the AirFreight Agents.
By default you should always try to merge One to One Data

I was'nt aware that someone had to register simply to read a discussion - I agree - I don't like it either - I would not have posted the link if I had known - Sorry.
I will ask my colleague who is a moderator why they feel its neccesary

Thx again 4 taking the time 2 read

GW