Ask Your Question
0

Is there a simple way to automate moving of a record between tables in a database?

asked 2015-12-30 09:02:28 +0100

PeterOL gravatar image

updated 2016-01-02 18:53:44 +0100

Alex Kemp gravatar image

Hi. I'm a green user trying to develop my first database in LibreOffice Base 5.0 under Windows 10 (English). I have two tables - Applicants and Members. 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?

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

I appreciate any help.

Peter

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-01-01 21:32:47 +0100

frofa gravatar image

updated 2016-01-02 01:51:43 +0100

Hello Peter:

The basis of your argument for having two separate tables (to track Applicants and Members) is not really that compelling:

1.

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!

2.

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'.

3.

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.

edit flag offensive delete link more

Comments

That is very helpful. Thank you for taking the time to explain. Having just read the page on voting and karma I would upvote that if I could (still don't quite understand why I need karma to express appreciation of the answer!). I was a bit hesitant to mark the answer as "correct" (Who am I to judge the accuracy of an expert's answer?) but have done so anyway to suggest that I'm satisfied with it. I'm sure I'll get to understand the system philosophy better in time ...

Thanks again. Peter

PeterOL gravatar imagePeterOL ( 2016-01-01 23:58:41 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-12-30 09:02:28 +0100

Seen: 122 times

Last updated: Jan 02 '16