Hello Peter:
The basis of your argument for having two separate tables (to track Applicants and Members) is not really that compelling:
-
Why don’t I have them all in the one table with a membership flag? Because once they are a member I will add a large amount of extra data, and I don’t want to take up all that space for people who may never become members.
The underlying database engine of Base (HSQLDB) is very efficient storeage-wise and I don’t think blank (null) column values take up much space (if any) in memory or on disk. Indeed having two tables might even take up more storage (because each table has some some admin overheads). It’s a minor issue anyway unless your database is going to be huge!
-
Also it makes it easier to interrogate the member table if I don’t keep having to add the membership criterion. There will be thousands of members.
Your queries would be stored in the database (and available from Base’s query list) so to produce a list of Members will not require you to repeatedly ‘add a membership criterion’.
-
When an applicant is accepted I would like to be able to click a button and move their entire record from the Applicants table to the Members table. Is this possible? How do i do it?
It is possible, and do-able, but not entirely straighforward. Essentially the ‘move’ would require chosen records/rows to be copied (INSERTED) into the Members table and deleted (DROPPED) from the Applicants table and no doubt you would want this process to be performed though a FORM. To streamline the process you would likely want to create a ‘move’ button that would trigger a script (macro) to perform the above operations on the selected rows. If you are a ‘green user’ (as you say) that might be ‘challenging’ (not that challenges are a bad thing!).
Having made a few Membership database applications myself over the years (using Open/LibreOffice), I have always found it simplest to use a primary table to track the names and contact details of the people (enquirers/members) and use a couple of date columns to record the JOINED DATE and EXPIRY DATE (blank if a person is not a Member but just an ‘enquirer’). Your needs might be a bit different, and a flag field (as you mention) might also do the job. Multi-table solutions might also be required in certain circumstances, for example to track Members’ subscription payments, multiple phone numbers, and so on. But try to keep things as simple as possible. A tutorial for setting up a club membership database is here.
