Database Design

users
#1

I have no experience designing a database. I hope this list is a satisfactory forum for my questions.

My son delivers a variety of newspapers with some subscribers getting more than one publication and with each publication's deliveries varied according to the day of the week.

I want to construct a database that will permit me to use eMail to send him a PDF file for a day's delivery list including instructions for driving the route. On a bad day I must be able to quickly apply as many as ~25 of these subscriber changes to the database:

• new subscription starts
• old subscription stops and restarts
• add/alter special delivery directives

I also need the ability later to resequence one or more subscriptions relative to the detailed driving instructions and to alter those driving instructions.

How much of this can be done using Base? What are the "gotchas" and design requirements? I expect that I will need a table of driving instructions, a table of publication delivery schedules, and a table of subscriptions with some kind of hooks interconnecting them.

I'm perfectly willing to take any discussion this may start off list after the initial round of responses if requested to do so with the understanding that I'll report back my final conclusions.

#2

My $.05 (.02 adjusted for inflation):

I'm seeing 3 tables

SUBSCRIBERS
SUBSCRIBER_ID
SUBSCRIBER_NAME
SUBSCRIBER_ADDRESS

PUBLICATIONS
PUBLICATION_ID
PUBLICATION_NAME

SUBSCRIPTIONS
SUBSCRIBER_ID
PUBLICATION_ID
SUBSCRIPTION_START
SUBSCRIPTION_END
SUBSCRIPTION_DAYODELIVERY
SUBSCRIPTION_SPECIALINSTRUCTIONS

#3

Also, I wouldn't even bother with putting up a table of driving instructions. I'd hook into Google's map driving capabilities. Don't reinvent the wheel unless you absolutely have to.

#4

Hi James,

I want to construct a database that will permit me to use eMail to send him a PDF file for a day's delivery list including instructions for driving the route. On a bad day I must be able to quickly apply as many as ~25 of these subscriber changes to the database:

The updates will be the most problematic, unless you are familiar with SQL - Lo doesn't have an "update" function button like you might find in MS Access, for example. For this, you would probably have to write a macro attached to a button on a data entry form. Having said that, if the updates are different for each customer, having a single update function wouldn't be of much use to you anyway.

• new subscription starts
• old subscription stops and restarts
• add/alter special delivery directives

All possible with Base forms (which are ultimately only Writer documents with form controls in them bound to the database).

I also need the ability later to resequence one or more subscriptions relative to the detailed driving instructions and to alter those driving instructions.

Not quite sure what you mean by "resequence" - do you mean the order in which the delivery takes place ?

Integrating driving instructions into the mix would seem to add a level of complexity here. How would you hope to achieve this ? There is no magical route recalculation logic in Base, so either you would have to use set instructions from a fixed starting point, or find some way to integrate dynamically obtained coordinates and redirections (from a GPS tracker ?), e.g. using macros or some other supported scripting language. No doubt this would be possible, given time and development (and money if the development is paid for), but is it worth it ?

Alex

#5

I wish to thank those who responded. I will play with your suggestions and take your warnings to heart.

At least for now, my son wants to go a different route with this by using an XML file to store the data and one or more Perl scripts to manage the XML file's content and to generate and to eMail an HTML formatted delivery list for the day. He's talking about sending the day's transactions (e.g. delivery starts and stops) via eMail from his iPad and receiving back the day's delivery list. He feels comfortable with the technique.

Again, I thank you for the thoughtful responses.