Best date implementation?

[pgerlier]

I am still experimenting with a native SQL data storage. I can generate a schema from the JSON description of the various genealogical objects (Person, Event, Family, …).

Until now, I postponed management of dates.

In the JSON schemas, dates are inserted via Date.get_schema() which embeds the Date schema in the “outer” JSON, e.g. a Date is fully duplicated inside an Event as a subschema.

I have then two options:

• do the same, i.e. duplicate the Date COLUMNs in every TABLE using a Date
  This simply requires an adaptation of serialize() and unserialize() methods.
• create a separate table for dates and replace the Date columns by a handle
  The aforementioned methods need also to be adapted but it probably simplifies reloading and saving dates.

The second option eventually suggests that Date records may be shared between several genealogical objects. Does this make sense? Are there contexts in which several records are so intimately related that they in fact make a single “meta-record” and a single date for them all is meaningful?

If the answer is “no”, both options are open. Which would be the best from a logical point of view or implementation robustness (fault tolerance, inconsistencies in records, …)?

[pgerlier]

I answer my own question after implementation trials.

It seems to be easier to create a separate SQL table for dates even if we retain the present management where get_schema() duplicates the object structure in the genealogical record. When loading or saving, the sub-record is replaced by a handle pointing to the SQL date record.

Some provisions must be made to avoid delete/recreating date records continuously but this is quite elementary.

I also came to the conclusion that data records should not be shared both on the ground that shared dates will become a nightmare and that except in exceptional cases shared dates don’t make sense (as a date per se is not a genealogical record; an event is and can be shared).