OpenEuphoria

2. Re: Setting up a simple database using EDB

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Jan 27, 2005
• 414 views
• Last edited Jan 28, 2005

Alex Caracatsanis wrote:
> 
> I need to create a database of CUSTOMERS. Each customer has a unique number -
> ID - going sequentially from #0001 to #9999. We record the SURNAME, FORENAME,
> MIDDLE_NAME, DATE_OF_BIRTH, ADDRESS, and TEL_NO.
> 
> Using EDS, I think it would be correct to make CUSTOMERS the TABLE - yes?
> What should the KEY be? The ID sounds ideal, but then how do we search for 
> customer Smith, John James; born 01/01/2005; at ADDRESS; etc... ?
> 
> Sometimes we need to find a particular ID (Eg: "Get the details for customer
> #00345"). But usually we've forgotten his ID, and our question is "Get the
> details for Smith - I forget his first name". Occasionally we're left a 
> garbled message to ring TEL_NO xx xxxx xxxx. Our task then becomes "Get the
> details for the customer with that number".
> 
> So what should be the KEY, and what should be the DATA, in a database that
> will make it possible for us to find that customer using any of his details?
> And what routine(s) will make that search possible?

You can do this with EuSQL.  I recommend using my Euphoria Database Browser
(EDB) to set up and administer the database.

First, create a new EuSQL database (it's really just an EDS database with
some added metadata).  Then create your table named CUSTOMERS, and select
it.  Then go to the 'Table Definition' tab.

Here, you can define your fields.  The first field will automatically
be the primary key--ID is probably the best.  If you want to be able to
assign ids to customers, then choose either Integer or Atom for the 
data type.  If you're happy to let EuSQL start at 1 and increment as 
customers are added, you can use the Autonumber data type.

Then enter your other field names.  Order isn't really important, although
it will determine how the fields are physically stored in the table.
You'll probably want datatypes of Text for these fields.  You can also
use the EuSQL concept of sub-fields for some of them (name and birthdate,
for instance):

NAME.SUR
NAME.FORE
NAME.MIDDLE

DOB.YEAR
DOB.MONTH
DOB.DAY

Then, you can refer to the field as simply NAME or DOB, and the resulting
data would be either
  {"SurName", "ForeName", "MiddleName" }
or
  { Year, Month, Day }

And then click Save Table.  If you know that you'll be doing many lookups
on Surname, you might want to create an index on that field.  This will
store extra data about that field, and make queries searching for a 
specific value much quicker, but it will also slow down inserts, updates
and deletes, and will make the file size larger.  You can create or delete
an index at any time, so it's not critical that you create them up front.

You can enter data directly into the table using the Data tab.

You can try out queries on the SQL tab.  So, to look for someone named
'Smith':

   select * from customers where name.sur like 'Smith'

This will return all the entries in the table that have a surname of Smith.
I recommend looking at the EuSQL docs to see what SQL is supported, and
what you can do with it.

Matt Lewis