Table structure?

[Joakim Schramm]

Ruslan,

I am coming back to this reply of yours as I have seen something with binary
links which is not so good, at least not for me :-( 

> -----Original Message-----
> From: valentina-bounces at lists.macserve.net 
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of 
> Ruslan Zasukhin
> Sent: 29 April 2006 15:37
> To: valentina at lists.macserve.net
> Subject: Re: Table structure?
> 
> 
> IF you use Binary Links, then it is NOT important what fields 
> you have in table. 
> 
> Linking happens simply by RecID values:
> 
>     Country                         Place
> --------------------            ------------------
> RecID   Name                    recID   Name
> 1       Albania                 1       Hamburg
> 2       Bulgaria                2       Berlin
> 3       Germany                 3       Munich
>                                 4       Leipzig
>                                 5       Sofia
> 
> 
> AGAIN, 
>     * RecID field exists automatically in each Table
>     * RecID use ZERO space on disk.
> 
> 
> So to link records you make BinaryLink and add into it pairs
> 
>         .........
>         3       1
>         3       2
>         3       3
>         3       4
>         2       5
>         ..........
> 
> Order of pairs do not mater.
> 
> So you do NOT need at all such fields as CountryID, PTR_Country, ...
> 

Ok, here is a part from kernel documentation on binary links.

"Another Example. Here we have five tables which must be linked as M : M.
Using relational model or even ObjectPtr fields, developer need create 4
Tables that play role of links. Now compare to the second picture where this
task is solved using BinaryLinks. 

You have now only five tables instead of nine. But yes. You have now 4
BinaryLink objects. Have you win something? Answer is yes. Because
BinaryLink use about 2 times less of disk space than Table-Link and it is at
least 2 times faster."

Is this really true? I managed to establish binary links between my tables,
and for the links Country->Places DB size grow 4.6 MB, for Country->Zones
5.2 MB and for Places->Zones, which has to be M:M as there is many places in
zones and there is many zone records for many places, I just came to about
2000 places of 220 000 before the demo limit of 10 minutes stoped it and
then DB had increased with anouther 145 MB!? Removing my old key fields and
compact DB just removed about 5MB.

I think Binary links is good, but maybe not for this sceenario. Maybe better
to split zone files into one for each country, and/or maybe better to use
ObjectPtr? This is how the original data structure is in text files, which I
read. 1 file for for country names, 1 file for places in each Country (so
there are many place files), and the same for TimeZone data, 1 file for each
countrys zone data which can have 1 to 300+ internal sets of date ranges.

Either I am doing something wrong if this increase is unnormal, as for
CountryHasPlace there are 279 RecId on One side creating links to 207391
places on the Many side, I guess that will be equal to 207391 Binary links
between these 2 tables? And for Country-Zones this figure is 279 and 243126.

But I think I have wrong structure for this Zone data, as it really is like
each place only can be in 1 zone, but the data for this zone is Many records
- sometimes only few but other times 100+

Just to picture again how the data is used. User lookup up Place, either by
search or picking from a list. For search this can be limited to Country if
selected but if no country selected search is on all places. So the country
here is really just a critera to limit search or not. From list, Places the
country are always selected to set criteria for Places to display as
displaying 207 000 places in one list isn't useful.

So the returned place is connected to a country and this country is then
used to lookup zones belonging to country. Each Place also have an zone
identifier to pair with which of the country's zone to use. From this zone a
value is looked up based on a date and time. The data and time data is from
user input.

Did you know by the way that Russia have 76 TimeZones? This is not of today
of course, but my data is not only for today but also historical AND in the
future.

Well too nice day today to sit in front of computer so will look at
restructure later, but I think I really need to restructure db but maybe
even then binary links is not right here.

And thanks for all your help by the way.

Joakim


> 
> --
> Best regards,
> 
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
> 
> Valentina - Joining Worlds of Information http://www.paradigmasoft.com
> 
> [I feel the need: the need for speed]
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>