Solved

tables for warehouse management project

Posted on 2014-03-09
7
281 Views
Last Modified: 2014-03-23
as per the prior advise of our moderators , please look at the attached file for the description of the project , containing tables definitions
don't worry about the long description ,
the question here is concern only for the table structures ,
is it ok ?  , or do you have another suggestions ?
then , if i need any help for functions or the forms , i will open new topic for each
thanx
whs-management.docx
0
Comment
Question by:NiceMan331
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Expert Comment

by:David
ID: 39918496
Nothing that my eyes caught, but including an entity relationship diagram would greatly help (next time).  I observe you are not showing any partition usage.
0
 

Author Comment

by:NiceMan331
ID: 39918547
thanx
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39918705
Perhaps you could use a real data model.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39918811
I'm a bit concerned about three different master tables sharing a single detail table (TRAN_D).   That can work, but there are disadvantages to this approach:
1. You won't be able to define all of these columns then as required: PR_ID, PO_ID or RCV_NO but logically, you would need at least one of them to be not null.
2. When you ever want to query (or report) on details someday for just one of the three master tables, you will force Oracle to read all of the details records for the other two types of master records too since they will be stored together in the same data blocks of the TRAN_D table.

I'm also concerned about the Posting_Date and Posted_By columns that I see in a few of these tables.  I'm guessing that means these records will be created with null values in these columns, and later these will be filled in with non-null values.  That leads to two problems:
1. finding the unposted records efficiently (since Oracle does not index null values)
2. causing "row chaining" when these records no longer fit in their original data block after the the Posting_Date and Posted_By columns get filled in.

You can avoid problem #1 by adding a column with a name like "NEEDS_POSTING" and a default value of "Y'.  Then add an index on this column, and instead of querying for records "where posting_date is null" (since this forces Oracle to read every record in the table, and that search will get slower and slower as records get added to the table) you query instead: "where needs_posting = 'Y'.  (If you want to keep this line: "and posting_date is null", that is not a problem, but it doesn't add efficiency.  You also need to change your update logic so whenever the Posting_Date gets set to a non-null value, you also update the Needs_posting column to null (so the index on this column stays small and efficient.)

With ASSM (Automated Space Segment Management) in Oracle10 or Oracle11 databases these days, you may not get many "chained rows".  But, you will have to check for those occasionally, and rebuild the table(s) where you see these occasionally.
0
 

Author Comment

by:NiceMan331
ID: 39919852
markgeer
thanx for your advice
1- regarding single details for 3 msters ,
actually when posting PR , user will fill PR_ID In detail table
when poting p data , user must post all , PR_ID & PO_ID in detail table
lastly when posting delviery , user must post all PR_ID,PO_ID,RCV_ID , to link each other
but i have no problem , i will let them 3 details , one detail for each master , same result , but just i want to save creating tables

2-regarding L posting_date & posted_by , those fields will not be null , they should be filled automatically once posting any record , postinf_date will be sysdate by default
and posted_by the user_name
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39920381
"but just i want to save creating tables" ?  That may turn out to be a very expensive decision!  Deciding how many tables to use should be based on long-term data usage and access.  How much (one-time) set up effort is required by you should not be much of a factor here.

So the posting_date and posted_by fields will be filled in when the records are created?  If that is true, then I see no problems with those fields/
0
 

Author Comment

by:NiceMan331
ID: 39922774
ok thanx
i already created the other 2 details tables
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

717 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