Can I have polyamorous tables?

[Steve C]

Is it okay to have a Table in a One To Many (with enforce referential integrity) relationship to TWO other tables?

I tried it and it works but I am worried about problems later on. So, instead, would it be better to I create a duplicate of the Table on the “one” side of the relationship so that both the “many” sided Tables have their own “one” table to have a (monogamous) relationship with.

In my db I have a table for “Points” which are names of places in London (there will be about 20,000) and I have another table for “Runs” (which are learned routes across London (there will be about 700)). Both Points and Runs have Postcodes and I planned to use just the one Postcode table for them.

 

[gemma-the-husky]

no, you should be fine -

however, it is debateable whether you need a separate post code table at all.

it depends how many points/locations share postcodes. personally i think i would put the postcode in the points table, and if you get a few duplicates it's hardly an issue.

a route will not need a postcode - a route ought ot be a succession of points.


you probably need a route details table though - maybe these tables


route
route details (points, and some extra field to determine the order of the route)

points (including post code)

 

[Steve C]

Thank you for your reply.

You are right about postcodes and I can see an input mask might be better. But, I chose a separate Table for postcodes because there are only 119 possible postcodes in London (E1, E2, and so on). Each postcode is associated with an area (E1 = Mile End, Stepney: E2 = Whitechapel Bethnal Green, Shoreditch.

I would like the areas associated with each postcode to display on a form to help me learn how areas relate to postcodes having only entered, say, E1.

Lots of places are called by their area name (e.g. Mile End Underground Station), learning just the postcode (E1) won’t be enough (although it is in E1, I will never be asked for the Underground station in E1).

You are right again when you say that Routes are a succession of Points - what else could they be?!

I must learn 320 seemingly random routes (called Runs) between two fixed Points all over London. And I have to know the routes from the end of one Run to the beginning of the next (oddly, these connecting routes are not called Runs (or routes) and don’t seem to have a name).

Beginnings and ends of Runs are, as you say, Points and have a postcode. But, I separated them from Points because it seemed logical. Runs will never change whereas Points come and go all the time as, for example, restaurants close down and open or change names. In total there will be about 20,000 Points to be learned (plus 25,000 streets). Runs seemed like a different category of information.

My Runs table has only a memo field the actual route. So Run 1 memo field reads;

Manor House Station N4 to Gibson Square N1
LOL. Green Lanes
R. Brownswood Road
L. Blackden Road
Fwd. Highbury Park
Fwd. Highbury Grove
R. St. Paul’s Road
Comp. Highbury Circus
R. Islington Park Street
L. College Cross
R. Hanbury Street
L. Milner Place
Fwd. Milner Square
Gibson Square facing

I must learn this list of road names and be able to say it out loud entirely from memory.

I can see a table having these road names would be a good idea – and I may do that – but the order will never change because it’s the straightest route – I just have to learn it.

Later on I will want to associate Runs with other Runs. For example, the end of Run 32 and the beginning of Run 8 are very close to each other on the map, I will want the db to associate Points around 32 & 8 together – of course, I will have to have a field in the Runs Table to record that 32 & 8 are linked spatially. I don’t yet have a clear understanding of how Expressions work and how to have forms return records in a subform based on the value in a field in the main form. I’m still learning this at the moment.

Sorry for such a long answer.

 

[jdraw]

Memo fields may not be your best choice. I recommend you investigate the use of separate tables -routes, routeDetails and points as Dave suggested.

Post codes at best are area locators. They are not point locators.

 

[Steve C]

Thank you for your post jdraw.

Oh dear.

Seems like I am disagreeing with Dave and it’s only my first day!

And, it gets worse, because now I’m going to disagree with you too jdraw!!

Oh dear Oh dear!

I would love to have your help please gentlemen with this database. I apologise if I seem to ignore you, I really am not ignoring you. Please understand – I haven’t Scooby doo what I’m doing.

You are right jdraw, postcodes are area locators, they don’t locate a point precisely.

In London, lots of Points have the same name. For example; there are several restaurants called “La Trattoria”. The postcode is used by examiners to distinguish which “Trattoria” is required – so, I think, I have to have a PointPostcode field. In my Points Table

You are right too jdraw, when you say Memo Fields are not a good choice for data.

The db I want isn’t about the 320 Runs that Transport for London (TfL) (who are the taxi governing body) insist trainee cabbies know, because that’s never asked in an exam! The 320 Runs are only a framework to build upon – not the Knowledge itself. IMO, TfL’s prescribed route between to Points is only a tiny piece of data which won’t ever change and does not need to be manipulated, in fact, a msword file would do just as well for it. I have chosen a Memo field (called RunsRoute) in the Runs Table only as somewhere handy to put it. What’s more important is whereabouts the TfL runs start and end and how the Points relate to that; then, later on, how the Run (beginning and end places) (and so Points) relate each to the other.

The db is not of London road names, although, as Dave correctly said, a road name can be (is) a Point. The number of possible permutations for how road names connect together between any two points in London is in the trillions, attempting to capture that is too difficult even for most Satellite Navigation computers which routinely don’t produce routes as straight as a London Cabbie must know.

Instead my db is about Points (that’s Bars, Restaurants, Railway stations Police Stations Embassies bridges, buildings, parks (and some roads too), anything somebody might ask a London Cab to take them to)).

In exams, two Points are asked and I must identify where those Points are and then recite, out loud without hesitation or error, the straightest line between those two points, naming every street and junction on the way. If I go the wrong way up a one way street or turn right through a “No Right Turn” sign - the exam is failed. Most people take 3 or 4 years to learn the Knowledge – that’s if they are working at it full time. 7 out of 10 people who start learning the Knowledge never pass.

As you can imagine, I have a long journey and, statistically, the odds are against me.

The db I am asking for your help to create will help me to learn to all this stuff.

I really really do appreciate your input.

Sorry for rattling on too much – if you made it this far - thank you.

Thank you again.