tables for warehouse management project

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
NiceMan331Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
NiceMan331Author Commented:
thanx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MikeOM_DBACommented:
Perhaps you could use a real data model.
0
 
NiceMan331Author Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
"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
 
NiceMan331Author Commented:
ok thanx
i already created the other 2 details tables
0
All Courses

From novice to tech pro — start learning today.