i am currently designing a relational database based on the
non-relational database schema. i had tried to look for some books,
i.e. database systems, or database product trainings, found about
nothing to help solving my problems.
the following has two parts. i listed the requirement of the design
and some solutions i have had in mind. i need some comments and
suggestions for better solutions.
Part A: the customer scenario:
a) a customer has its data stored in a customer table.
b) this piece data is valid within a period, including the current
access datetime.
c) a user proposes some changes of this piece of data with an
effective date.
d) the outdated customer data needs to be saved as customer history
and some program can look it up.
solutions:
1) create 3 customer tables: cust-hist, customer, cust-new.
pros: easy to understand, easy to select (for customer only), small
table.
cons: 3 tables with the same design which means need to make 3
changes,
need to select 3 tables, need to copy the data from one to
another.
2) create one customer table: customer.
pros: easy to maintain. it's neat to have this design since other data
objects has similar design as customer.
cons: slower to select (need to compare the effective date and
terminated date), a lot larger files.

Part B: the manifest scenario:
a) a master manifest data table stores attributes that are the same
everyday, again, it has manifest-hist, manifest, and manifest-new.
b) users can assign various operations on each date on a particular
manifest.
c) for some reasons each date on each manifest there would be more
than one versions of data and the users can choose what to use in
actual operation.
solutions:
i have only one solution:
create a master manifest file with similar design as customer; create
a manifest-date-version file for each date, each manifest, and each
version.
pros: this is the only solution i have.
cons: the manifest-date-version table size is huge! and selection
takes quite long.
i am looking for a better solution for this scenario.

hope to get some feedbacks on these complicate problems, and would you
please recommand me some good books about actually database design
(other than those theories or database product trainings)? and where
to download some sample database (like the movie database, or employee
database demo, etc.)
thankx a lot.
from alan.

Hi Alan,

you can see a customer master file demo app. online using all the scenario
you want

http://demoroom.pro-org-edv.com/bin/index.htm
(username : 73400 password : 298733 this is an english access. If you
need this in an other language like german, french, portoguese, send me a
request)

By the way this is a 100% browser based ERP system containing more than 1000
Forms and
menus with appr. 10000 Fields which are created using dynamic pages

Version Sample:
- you drill down the application menu :
WWS/PPS Mananger / Customers / Edit Customer Master Data
-select a Customer (maybe thru search, or create a new one)

now you have multiple options:

- change an input field i.e. Name and save, rechange and save

-or click on the last button 2nd column (clock symbol)
in here you can set all the data that has an effective date. a background
job checks dates and change the data on effectivity dates.

All the versions and change protocols will be save in the background.
To see the changes in the past simply press the F1 key when in the field
(the view is limited the 10 changes but the file is bigger)

actualy we do not need to do anything to perform that. our save design and
the form definition perfoms this task dynamicly.
And yes, there are internaly 4 tables that can be huge because everyting
will be protocoled (but reorganised after given time)

In the background we are using the objectoriented postrelational database
Caché from Intersystems
(I think you can download a database demo version on www.intersystems.com )

Ditmar


"Alan Zhong" <alanchinese (AT) yahoo (DOT) com> schrieb im Newsbeitrag
news:b09c98a8.0407141023.7e962488 (AT) posting (DOT) google.com...