one or two tables?

Results 1 to 2 of 2

Thread: one or two tables?

  1. #1
    Join Date
    Dec 1969

    Default one or two tables?

    Hi-<BR><BR>I developing a requisition/PO application and need some input.<BR><BR>Description:<BR>Right now I have a single table holding the Requisitioned Part information. These are related through another table to the Requisition ID. A Requisition ID may have many RequestedPartIDs.<BR><BR>There&#039;s also an approval process. Until approved, a Requisition ID has a status of Preliminary. Preliminary Requisitions may have RequestedPartIDs added/deleted/quantity changed before being approved, or the entire Preliminary Requistion may be denied.<BR><BR>Question:<BR>Right now I am writing all RequestedPartIDs to a single table. This means that if a RequistionPartID is denied, that I have to delete the RequistionPartID (or IDs if an entire Requisition is denied).<BR><BR>Would it be better to have a separate table for Preliminary Requisitions RequestedPartIDs, then "move" the data to the Approved RequestedPartIDs list when approved? Right now, the only way to tell if a RequestedPartID is preliminary is to run something like:<BR><BR>SELECT Requisitions.ReqID, Requisitions.IsApproved, Requisition_Detail.RequestedPartID<BR>FROM Requisitions INNER JOIN (Requisition_Detail INNER JOIN Req_to_Request ON Requisition_Detail.RequestID = Req_to_Request.RequestID) ON Requisitions.ReqID = Req_to_Request.ReqID;<BR><BR>If I had a second table, I could just:<BR>SELECT RequestedPartID from UnapprovedRequestedParts; <BR>but then I&#039;d have to write queries to move the data from one table to the next if approved.<BR><BR>Is there a benefit one way vs. the other? Any comments are appreciated.<BR><BR>Thanks<BR><BR>Keith

  2. #2
    Join Date
    Dec 1969

    Default Depends on what you do more often...

    I don&#039;t think there&#039;s any hard and fast rule on this one.<BR><BR>But...I&#039;m not sure I entirely understand the structure of your tables.<BR><BR>You have a requisition.<BR><BR>It can have many requested parts.<BR><BR>You link the parts, which are in the Requisition_Detail table to the Requisistions table via the Req_to_Request many-to-many table.<BR><BR>Okay so far.<BR><BR>But then why would you ever delete anything from the Requisition_Detail table??? Isn&#039;t the at the table that holds (for example) the full details of a part?<BR><BR>Isn&#039;t it the many-to-many table that should hold status and quantity and things like that???<BR><BR>If a part found via Requisition_Detail.RequestedPartID can only be associated with a single record in the Requisitions table, then what is the point of the Req_to_Request table???<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts