Re: HELP, HELP!!! CONVERT A DATA STRUCTURE IN C# TO A ACCESS DATABASE


Hi Eder,

In a relational database, an idea like 'the set of cities' is modelled
as a table with one record per city. Let's call it tblCities, with a key
field CityID. Likewise, the set of modes of transport is modelled as
another table with one row record per mode (e.g. car, bus, train...).

I'm not absolutely certain that I follow the notation you used in your
example, but it seems that for each city you want to store all the
destinations reachable from that city by the various means of transport.
You could do that with a third table (let's call it
tblCitiesModesDestinations with three fields
FromCityID - foreign key into tblCities.CityID
Mode - foreign key into tblModes.Mode
ToCityId - foreign key into tblCities.CityID

For example, the facts that you can get from New York to London by boat
or plane might be stored as the two records
FromCityID, Mode, ToCityID
New York, Boat, London
New York, Plan, London

and you could retrieve the set of all cities reachable by plane from New
York with a simple query:
SELECT ToCityID
FROM tblCitiesModesDestinations
WHERE FromCityID = 'New York'
AND Mode = 'Plane'
;

(In practice you'd probably need to retrieve more than just the CityID
field, and the query would be more like this
SELECT C.City, C.Province, C.Country
FROM tblCitiesModesDestinations AS M INNER JOIN tblCities AS C
ON M.ToCityID = C.CityID
WHERE M.FromCityID = 'New York'
AND M.Mode = 'Plane'
;

Where things get more complicated is if you need things like 'the set of
cities that can be reached from New York with no more than three
intermediate destinations'. Here's an interesting article:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427



On Sat, 16 Jun 2007 09:19:00 -0700, Eder Andres
<EderAndres@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello:



Recently I?m migrating from data structures to databases. I?ve converted a
Customers, Products and PendingOrders collections into a Microsoft Access
2003 data base. But I must convert many other data structures.

Now, the problem is a graph. In this project, there are cities and
transportation means. Traveling to one city to another we must use a car, a
bus, a train, an airplane, or a boat but we can?t travel between two if the
transportation mean is not available, this is there are cities unreachable
using a car (like cities located in islands) , a airplane (the destination
city does not have airplane) and so on.

I?ve used the graph data structure as solution. The nodes are the cities and
the arcs are a relation between the city and the transportation mean. In
math, this is

Destination (city, transportation mean) = {city1, city2}

I?ve implemented this relation between two sets.

C: the set of cities

T: the set of transportation means

D: the set of destination cities given a city and transportation mean. It is
a subset of C.

For this purpose, the arc, called destination, is of the form

(startCity, transportationMean, destinationCities)

For implementation I?ve used list structures:

City1-------|---------àMean1---------------- à{city1, city2}

|-------àMean2---------------- à{city1, city3, city5}

.

.

.

City2-------|---------àMean1---------------- à{city3, city4}

|-------àMean3---------------- à{city1, city4, city7}

.

.

.

Where, basically, there is a list of cities, each city points to its proper
list of transportation means, and transportation mean point to its proper
list of destination cities.

| |--------->| |-------->| | | | |

|----->| |-------->| | | | |

|----->| |-------->| | | | |

|----->| |-------->| | | | |



| |--------->| |-------->| | | | |

|----->| |-------->| | | | |

|----->| |-------->| | | | |

|----->| |-------->| | | | |

|----->| |-------->| | | | |

I?ve red databases examples, but I don?t found a similar model and I don?t
know how to design this recursive relation in Microsoft Access.

Help for this problem!

e-mail me: monin712@xxxxxxxxxxx


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
.

Relevant Pages

  • =?iso-8859-1?q?Lessons_from__Bogot=E1?=
    ... Now, on the Building a New City tour, Mr. ... Africa's leading cities: Dakar, Senegal; ... Traffic congestion, inadequate public ... transportation, poverty and poor access to jobs and services are ...
    (soc.culture.indian)
  • HELP, HELP!!! CONVERT A DATA STRUCTURE IN C# TO A ACCESS DATABASE
    ... But I must convert many other data structures. ... transportation mean is not available, this is there are cities unreachable ... city does not have airplane) and so on. ...
    (microsoft.public.access.tablesdbdesign)
  • HELP, HELP!!! CONVERT A DATA STRUCTURE IN C# TO A ACCESS DATABASE
    ... But I must convert many other data structures. ... transportation mean is not available, this is there are cities unreachable ... city does not have airplane) and so on. ...
    (microsoft.public.access.gettingstarted)
  • RE: Attention Ken Sheridan
    ... is the correct row or would assume that there are cities called San Francisco ... LastName and City fields do meet this criterion ... "I have all 200 of our stores in one table" ... Each store relates to one hub only, but two stores in one City say, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: WC2014 host cities
    ... the two "green" hosts are ... city], and Cuiabá is an Amazon region city more than anything else. ... cities who support football better than them (I'm thinking here the ... As for the elimination games, ...
    (rec.sport.soccer)