data with versions.
Posted: Wed Jul 14, 2004 1:26 pm
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.
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.