Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tables for warehouse management project

Posted on 2014-03-09
7
Medium Priority
?
287 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

610 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