Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Database Structure Question


The Digital Man
 Share

Recommended Posts

O.K. I want to ask some questions before I get in too deep about how I should probably structure my database. I have a job management system in place and it currently tracks the jobs in progress and prints active jobs on a report that we go over in our production meetings. It has the customary CONTACT, COMPANY, etc. tables along with the following key tables. I've written the solution from the ground up.

 

JOBS

JOBS -> COMPONENTS

INVENTORY

 

The COMPONENTS table is a many to one relationship with the JOBS table which is why I listed it that way. A job can have more than one component in our manufacturing situation. For a typical example we may get an order in to manufacture 1000 Vinyl Tile Carry Boards.

 

Component #1: The 26" x 30" Carry Board which has rounded corners and a die cut handle is cut by computerized router out of 1/4" Sentra Material. We would order these from a supplier. Lets say COMPUTER CUTTERS.

 

Component #2: A 5" x 7" Color Photograph of the tile installed in a room. We call these roomscenes and they are offset printed and gloss filmed. We might order these from an offset partner down the street.

 

Component #3-#10: Would be the different colors of tile flooring that we would be cutting and glueing on the board. It might get FOUR 12" x 12" tile pieces of the color "Natural Earth" glued on the front of the board. Than we might glue SEVEN 4" x 6" pieces of the seven other different colors that particular style of tile comes in on the back side of the board along with the room scene.

 

We are currently using the COMPONENTS table in conjunction with the INVENTORY table to keep up with materials demand and whether we have the material we need on the floor to complete a given job.

 

To produce this job we need to do the following tasks.

 

1. Order (Or check to see that we have un-committed stock on the floor) for the carry boards. We stock certain sizes in anticipation of customer orders to provide better service.

2. Provide the customer a proof for approval.

3. Make necessary changes on the proof.

4. Receive an approval from the customer on the proof.

5. Order or request the necessary tile from the customer. (Can't be done until proof is approved as the layout affects how much tile is needed)

6. Order the necessary roomscenes from the offset printer (Can't be done until the proof is approved)

7. Receive in or pull from stock the necessary quantity of carryboards

8. Silkscreen both sides of the carryboards with the customer logo and tile colornames etc.

9. Glue the roomscene on the boards in the appropriate place.

10. Cut down the seven colors of tile that will get swatched on the back

11. Swatch the tile onto the carry boards.

12. Ship the finished carry boards to the customer.

 

Some of the tasks can be carried out concurrently while others have to wait until a previous task is completed. I am thinking I can set up a COST CENTER or TASK table and I want to ultimately have the people ordering the stock etc. putting in the dates when we are being promised delivery. Than I want the system to be able to calculate or schedule our delivery date of the finished product based on average times which I can supply it as to how fast various tasks go. I know this is going to be very complicated. And I'm going to have a LOT OF FUN writing it. I am not too concerned however. I just want to get started off on the right foot. Is there a particular structure I should seek to follow or are there any examples I might study? I understand having the TASK table but I am trying to wrap my brain around what other supporting tables or self joins will I need to be able to compute final delivery date by us once all the data is in on all the components of a given job.

 

Well thanks for reading the post. I am not looking for detailed coding here. I am looking for general direction and structure coaching.

 

Randy

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use