[Tutor] design question (Django?)

[Albert-Jan Roskam]

Hi Alan,

Thanks for your reply!


> Subject: Re: [Tutor] design question (Django?)

> 
> Some clarification please.
> 
>>  (1) Database design. Here are the database tables I think are necessary:
>>  tblPostalcode: pc6, streetname, nHouseholds, isVisitable, remarks
>>  tblCollectorSelection: collectorname, streetname, selectiondate
> 
> streetname is in both so presumably is the link between collector and the 
> postcodes? Can a steetname be in multiple postcodes? So if a collector selects a 
> street does he implicitly select multiple post codes?

Yes, a street name can be in multiple postcodes (typically odd and even street numbers of long streets have different postcodes). For example, postcodes (pc4) of the longest street of the country (in The Hague) are 2517, 2555:
http://www.postcode.nl/index.php?goto=postcoderesult&action=page&pagenum=0&address=den+haag%2C+Laan+van+Meerdervoort&TreeID=1 So, yes, the collector may select multiple postcodes when selecting just one street. Historically, pc6 is a concept from the mail service. One pc6 is (literally) a handful of mail for a mailman. One record in tblCollectorSelection = one pc6 selected by that collector. But since most people think in terms of street names rather than in pc6, the selection is made by ticking boxes with street names.

Come to think of it, I may also need a tblCollectors, with name, adress, postal code, phone number of the collector, preferredPostalCode (some collectors want to collect in another pc6 than where they live).
 
> Does a postcode only have a single street? (Ours can have several)

It's the same as with you in the UK/Scotland. Often one postcode has just one street, but there are exceptions.

> I assume isVisitable is a derived field that you are going to calculate each 
> time based on the number of collectors who have selected the postcode? 

isVisitable would be PO boxes, apartment buildings that do not admit any collectors at their main entrance,
industrial zones, dangerous neighbourhoods, and maybe a few more categories.

>If so it 
> may be easier and more efficient to have a mapping table to manage the 
> relationship of collectors to postcodes and another table for the collector data 
> (although you don't appear to be holding much collector data, which 
> surprises me... I assume the users will be collectors with login credentials 
> etc?)

yes, collectors are assigned to groups (of about 8 persons) and they should get login credentials (which are stored in tblCollectors (?)).

> As always when modelling data the important information is the relationships 
> between the entities and I'm not sure I understand
> the relationships between street, postcode and collector.

I think I have to make a diagram of this. This stuff is quite hard for me, but it's important to get the basis right.