1

I'm building a web application for a printing company and am trying to determine the best design for my database.

I have an orders table. Each order has many proofs. However, there are two different kinds of proofs: electronic proofs and physical proofs. Furthermore, if its an electronic proof, I need to store the image source, and if its a physical proof, i need to store a tracking number.

Every proof has many comments associated with it.

It would be nice if i could just have one proofs table, and one comments table, but what is the best way to keep track of the image source and tracking number based on the proof type?

I've considered creating separate tables for electronic_proofs and physical_proofs, but that would require making a separate electronic_proof_comments table and physical_proof_comments table.

The other option would be to have a single proofs table, in which case I could then have a single comments table. However, as I see it, that would require three support tables, proof_types, image_sources, and tracking_numbers.

Any thoughts on the best way, or an even better way of solving this problem?

Derek Harrington
  • 457
  • 4
  • 13
  • Quote: "I've considered creating separate tables for electronic_proofs and physical_proofs, but that would require making a separate electronic_proof_comments table and physical_proof_comments table." Why not do that? Seems like the simplest solution imo. – Msonic Mar 06 '12 at 21:44
  • 2
    @Msonic: You could use a [polymorphic association](http://guides.rubyonrails.org/association_basics.html#polymorphic-associations) for that. – mu is too short Mar 06 '12 at 21:51
  • @Msonic It just seems like there is a better way that I could go about this. I feel like doing it that way gets too messy with the larger number of tables that are required – Derek Harrington Mar 07 '12 at 00:59

3 Answers3

4

As mentioned in another answer, you only need one table and would simply leave one of the fields blank, depending on the type. However, the difference in my answer would be to relate the order details to the proof rather than the proof to the order details (or order item in their example). Doing it this way allows you to have multiple proofs per order detail.

This is how I would set it up if I were you:

ORDERS

  • OrderID (PK)
  • CustomerID (FK)
  • Etc...

ORDERDETAILS

  • OrderDetailsID (PK)
  • OrderID (FK)
  • ProductID? (FK)
  • Etc...

PROOFS

  • ProofID (PK)
  • OrderDetailsID (FK)
  • ProofType
  • ImagePath (just path, not image)
  • TrackingNumber
  • Etc...

COMMENTS

  • CommentID (PK)
  • ProofID (FK)
  • Comment
  • Etc...

It would probably also be wise to break ProofType into it's own table, but will work without. If you were to do that, you'd create a ProofType table and change the "ProofType" field in the "Proofs" table to a foreign key referencing the "ProofTypeID" field in the new "ProofType" table.

Hope this helps! Good Luck!

Ricketts
  • 5,025
  • 4
  • 35
  • 48
  • If you include Proof ID on the Order Detail table, you have a many to many relationship *already*. There is no need to include an order detail id on the proof table. – N West Mar 08 '12 at 15:31
  • But as I said in the comment to my answer, if the true relationship is 3-level (order > line > proof), then this would be a better solution. – N West Mar 08 '12 at 15:40
  • As I understood, it would be 3-level and that would be common for a printing company. The order table will hold the general order info. The order details would hold the different items (Business Cards, Flyers, Postcards) and their attributes. Then each of those could have multiple proofs. – Ricketts Mar 08 '12 at 16:11
  • It is 3-level, and this looks like a good solution. Thanks for the input. We're going to use this schema. – Derek Harrington Mar 08 '12 at 20:17
1

I agree with @Ricketts (+1). The tricky part is whether to have columns ImagePath and TrackingNumber in the Proof table, or to normalize them out into separate tables. (Normalize, because they don't depend on the primary key, they depend on the primary key + the proof type column.) If these are the only two columns that are proof-type-specific, then you're probably ok storing them in the single table... but that ImagePath makes me nervous, particularly if its not an image but an actual sizable chunk of binary data. It might make sense for a number of reasons to store that data separately in its own table, but not move TrackingNumber out as well.

Other considerations: what's the ratio between proof types? How is performance likely to work (particularly if there's a BLOB involved in your data requests?) You have to weigh and perhaps test these considerations before making your final decision.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • 1
    In my example, I have recommended that he does not store the binary data in the database itself. I own a print design and web development (obviously) firm and we deal with a lot of printers. Every printer I've ever seen provides proofs in either PDF or JPG formats. My recommendation to him is that the proof document is stored on the file system of the server and the path to that document is stored in ImagePath (would probably be better named DocPath). Then in their app, they simply reference the path to the proof rather than parsing the binary data itself. – Ricketts Mar 08 '12 at 16:05
  • I wasn't going anywhere near that one--there must be dozens of questions on that subject on SO, and there rarely is a black-and-white answer. (Base question: if the DB has a file path, but for whatever reason no file is found there, what then?) Alternatively, have you ever worked with the Filestream datatype? I haven't, yet. – Philip Kelley Mar 08 '12 at 16:21
  • We did implement @Ricketts solution, but we also decided to normalize TrackingNumber and ImagePath into separate tables. We're also storing only the image path. Thanks for the input (+1). – Derek Harrington Mar 08 '12 at 20:16
0

I would expect that proof type is just an attribute of the item, as is the tracking number and image source, correct?

It's similar to a situation where some items may come in sizes, but some don't - you just don't populate the attributes that don't matter for that specific type of item.

Also, note that with two different "proof" tables, your order line item table now has to deal with two different entities.

Something like this should be doable for basic use...

ORDERS
 Order ID (PK)

ORDER ITEM
 Order Item ID (PK)
 Order ID (FK)
 Proof ID (FK)

PROOF
 Proof ID (PK)
 Proof Name
 Proof Type
 Tracking Number
 Image Source

COMMENTS
 Comment ID (PK)
 Proof ID (FK)
 Comment Text

You can create lookup tables for proof type, tracking number, and image source if necessary. It really depends on how far you want to go to match reality to relational theory.

N West
  • 6,768
  • 25
  • 40
  • 1
    this doesn't allow for an order item to have many proofs, which i need to be able to do – Derek Harrington Mar 07 '12 at 22:23
  • Yes it does. Your ORDER ITEM table is the many to many relationship between the ORDERS table and the PROOF table. – N West Mar 08 '12 at 15:29
  • Unless you actually have a 3-level relationship - Each Order has many order items (or "products ordered" etc), then each of those "products ordered" is composed of multiple proofs. Your question implies that each *order* has multiple proofs, not each order item. – N West Mar 08 '12 at 15:33
  • I see. I should have been more descriptive. Sorry for the confusion in the question, but i do indeed have a 3-level relationship, as the situation you just described – Derek Harrington Mar 08 '12 at 19:54