Now that we know where to store the data, we also have to look into how we store the data. The database model show in Figure 10.1, “SySeEns database model” seems reasonable:
This table holds the basic user information. The UID is only used internally for reference from the other tables. The name and password are used for authentication via the SyncML layer. The password is stored in clear text. The Group property holds the group that newly created entries will belong to.
There are two ways a user can belong to a group. One is having the group entry in the Users table. The other way is an entry in this table
This table maps the client (local) UIDs (LUID) to the server (global) UIDs (GUID) for every client. This table will be quite large, since one entry has to exist for every possible mapping. If an item is soft-deleted on a client then the LUID field will be empty.
The Client table contains information about every client syncing with this server. The numeric client ID is used internally. The LastSync is used to find out which objects have changed. Also the clients device information is cached in here.
This table holds the actual data. The GUID is what would have been the UID within the data field. TID holds a reference into the types table. LastName is used for speedup as explained in the section called “Special last name handling”. LastMod is used to find out if this object has been changed since the last sync. UID, GID and Permissions are used for access control. Finally, Data holds the actual data in the specified format.