Table structure?

[Ruslan Zasukhin]

On 4/28/06 11:31 PM, "Joakim Schramm" <joakim at astrocalc.com> wrote:

For info: am I right that in your old app you have not use SQL and are used
to just API access to a database ?

--------------------
> This is an "Atlas" database (none graphical, just data) so I have following,
> in a relational schema:
> 
> Table1 (A place in the world)
> Place|LatDeg|LatMin|LatSec|LatCompass|LongDeg|LogMin|LongSec|LongCompass|Cou
> ntry|TimeZoneID
> 
> Table2 (Timezones in the world)
> DateTimeOfChange|ChangeVal|Ptr_Country|Ptr_TimeZoneID

> How it works in practice in program is, a place are picked, either from
> returned SearchCollection or from List.

Ok. So you have one select place, i.e. RECORD of T1.
You know its RecID.

> Next, based on country AND ZoneID (most countries have just 1 but some have
> over 300) 

One country have 300 time zones?

Dummy question, isn't we have only 24 zones on earth?

> a timezone value are returned with passed DateTime as criteria.

Okay, so ONE record in T1 is "linked" to some set of records in T2
But condition based on 2 fields.

> Quite simple yes in Relational,

> but I like to learn and use something new and is still unfamilar with
> Valentina. My way of thinking though is if I learn and understand something
> difficult, then I understand everything, sort of...

Okay, lets try.

Look on T2.

Table2 (Timezones in the world)
   DateTimeOfChange
   ChangeVal
        Ptr_Country             <<<<<<<<
        Ptr_TimeZoneID          <<<<<<<<


The last 2 fields present here ONLY BECAUSE  you need establish link to T1.
Am I right ?

Remember pictures from Valentina WIKI about links?
In Relational model Tables have USEFUL fields and UTILITY/HELPER fields.

Am I right that this is enough for T2 to bring USEFUL info:

Table2 (Timezones in the world)
   DateTimeOfChange
   ChangeVal

I think enough. Then be happy. Just only you have win again a lots of disk
space 
        Ptr_Country            VarChar?  Avg 7 bytes
        Ptr_TimeZoneID         Ulong ?   4 bytes
    
        (7 + 4 = 11) + (11 * 1.5 = 16) = 27 bytes.

        13MB of garbage, go out from my disk!!  :-)

Aha, and now question how to link records of T1 and T2?
Answer - using Binary Link.

How to build it? Easy:
At first build table as you have, then write special function to iterate
records and build BinaryLink records. Then DROP that 2 fields. You do not
need any more.    


P.S. Also it seems good idea to me to extract separate table COUNTRY,
    and put into your T1 only ObjectPtr to it.


-- 
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]