?
Solved

taable_design

Posted on 2009-02-11
8
Medium Priority
?
260 Views
Last Modified: 2013-11-13
if I have a database with a lookup table for states used for validating data on screens.
REF_STATE_CODES
-------------------------
state_code,
state_name
active_yn
created_date
created_by

Someone wants to add a new column (default quantity) for each state for some invoicing estimates. Does it make sense to add this to this table or create a new table for that specific purpose especially since that quantity will be updated?
any good suggestions and arguments.





0
Comment
Question by:sam15
8 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23614626
A default is usually a look-up value, so there shouldn't really be a problem in having that column in your above table..

The quantity wouldn't necessarily be updated in this table frequently, it should be used as a initialization value in the application and actualy updates should go to a transactional table..
0
 
LVL 23

Accepted Solution

by:
David earned 1000 total points
ID: 23616654
A different POV:  as a lookup table (they're not making new states anymore....) this should be small, bitmapped, static.  I think your quantity, and also your active_yn boolean, are probably related to an order process far more than the U.S. geography.  Secondly, I am strongly opinionated that all data constraints and defaults should be defined in the table(s).  In some table X, you have a column QUANTITY with DEFAULT "<somevalue>".
Likewise, I trust your boolean column is constrained by being in ("N","Y").  Otherwise, does your program accept trash data such as "1" ?
0
 
LVL 3

Assisted Solution

by:garysadler
garysadler earned 1000 total points
ID: 23619433
I agree that there's no reason why you shouldn't add the column to the existing table.  One of Oracle's strengths is in handling concurrency, so even if the column IS updated frequently, it wouldn't be a problem for other sessions that just needed to read the values from that table.  

You might also consider using the "cache" clause when altering the table so that it stays in the buffer cache.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 2

Expert Comment

by:lucivero
ID: 23623729
I would agree with DVZ, a small static in core table will provide better performance. Since I don't expect we will be adding any new states, you should never have to update the program for changes.  Even if it only saves .5 second, it is worth it for any online process.
0
 

Author Comment

by:sam15
ID: 23666597
what does the "cache" clause do? and is this done when you create/alter the table.
0
 
LVL 3

Expert Comment

by:garysadler
ID: 23666961
The cache clause can be used in either the CREATE TABLE or ALTER TABLE statements.  It will direct Oracle to keep blocks holding the table data at the most-recently used end of the database buffer cache LRU queue following a scan of the table.  That will, in effect, help keep a small and frequently-accessed  reference table cached in memroy.
0
 

Author Comment

by:sam15
ID: 23671579
I thought oracle caches everything anyhow.

if i do select  * from state_table

is not the data cached?
what about if somone changed something in the lookup? would it know that and go to the DB instead of the cache.
0
 
LVL 3

Expert Comment

by:garysadler
ID: 23738179
Data is cached in Oracle's database buffer cache, but if you do full scans of large tables, it may be forced out.  Using the "cache" directive is just a way of helping to prevent that.  

Oracle keeps track of read consistency states so you don't need to be concerned about whether a particular data block is in memory on on disk when an update occurs.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Progress
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question