Relationship problem

[Stoyan]

Hi, this will be very stupid question but i'm still a newbie .
The database is for transport company.
I have 3 tables
tblClients:
ClientID-Autonumber
Client_Name-text
Client_Adress-text
Client_IBAN-text
tblCities:
CityId-Autonumber
City_Name-text
City_Postcode-number
tblOrders
OrderID-Autnumber
Sender_FK-number
Receiver_FK-number
From_City_FK-number
To_City_FK-number

I don't know how to set up the relationship between them.The Client can be "Sender" and "Receiver" and the the City can be "From" and "To".

 

[pr2-eugin]

Hello Stoyan, Welcome to AWF..

You have started on the right path, defining relationships. As without having proper relationships designed you will have problems like excessive coding, slow and complex queries. However, you also have to make sure that the data that you are trying to relate should not be complex by itself..

I am unable to understand what you mean by Sender and Receiver can be the same client.. Or City can be From and To.. I would advice you to go through the Document to understand a little bit more about relationships..

 

[Stoyan]

Ok my english is not so good but i'll try to explain.We have transport company with Trucks and we deliver different things /for example Toys/ from one City to another.
Here is the situation.
Client 1 can send to Client 2 toys From City 1 /London/ to City 2 /Fulham/
But other time Client 2 can send to Client 1 apples form City 2 /Fulham/ to City 1 /London/

 

[GinaWhipp]

If it were me...

I would have a Bill of Lading table to define the To/From, ie:

tblBillOfLading
bolID (PK-Autonumber)
bolBillOfLading (TEXT or NUMERIC [Your choice])
bolShipDate (Date/Time)
bolShipTo (Long - Relate to tblClients-cClientID)
bolShipFrom (Long - Relate to tblClients-cClientID)
etc...

 

[Stoyan]

I made it the same way but i think this isn't the right way to do it because bolShipTo an bolShipFrom relate to the same field it table Clients.

 

[Cronk]

On your relationships page, you can have multiple occurrences of the same table.

So add tblOrders, add two copies of tblClients with one linked to Sender_FK-number and the other to Receiver_FK-number

Set up tblCities the same way, only depending on your situation, you might need to have 4 copies, one for the Sender table, one for Receiver and two for the Orders (Sending city, Receiving city)

One other thing, I try to keep my field names as short as possible as long as I can not lose their significance.

For example, I would simply have ClientID in tblClient - it's obvious to me it's the primary key and I almost always use autonumber so it's evident. In tblOrders, I would just have SenderID and ReceiverID. My reason is that in any coding, there's a lot less typing.

 

[GinaWhipp]

@Cronk,

Using ClientID as a PK and FK means I have no idea which table I put it in, hence the Prefixes. A wee bit more typing but worth it when you have 50+ tables!

 

[Cronk]

Yes but if I were to see a field called ClientID in tblOrders, I would presume it was a FK, not a PK.

That is, my PK in every table reflects the table name. Any other ID field in that table would be a FK.

 

[GinaWhipp]

Okay, I see your point... I just like to know what table when I'm witing code as some of this code can get pretty lengthly. Thanks for sharing!