Solved

tables for warehouse management project

Posted on 2014-03-09
7
269 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 40
oracle global variables 4 52
oracle report printing 2 pages in one page 2 38
sql query 5 52
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now