Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Structure 3 Tables for Parts or just one

Structure 3 Tables for Parts or just one

Structure 3 Tables for Parts or just one

Tek-Tips Pro's

A little background....
I could really use some direction; we are building a custom database for my business, MightyPouch.com. We make custom cases for just about everything from scratch. We cut the materials and sew them.

Our business model is to offer the customer a completely customizable case, if the specify it's for an Iphone, the database will pull the dimensions for the Iphone and production will receive a cutlist for the job with all the materials the need. The customer can order just on dimensions too ie a phone with a hard case we build from dimensions.

I am trying to design a full database, from order importing from the website to printing a packing list and managing payments.

The question

We have a debate ongoing on the tables and relationship structure.
I may be over splitting tables. My Coworker and I are stuck in a debate as to how to organize our database structure. I am suggesting that all 3 component types be split in to 3 tables as the relationships attached show.

He is suggesting that all 3 of these tables be consolidated to one table even though they do not always share the same fields.

Who is right? My argument is that the 3 materials have different needs and all production lists and screen layouts will have sorted/grouped components. As we gather materials in different locations this will make it easier on production.

He argues that I simply need a field in the main table that is has the option of "1DPart", "2DPart", and "Component". This would allow me to sort the items for a cleaner layout for production.

He believes the seperate tables will bloat the database as 3  tables would have to be opened to add each part needed. I argue that the 3 tables will each be 1/3rd the size of his one table.

Other issues to consider

We will have code that when a line item is entered with a certain option the part/material is added to the build lists automatically.

In my opinion these parts will be added to the 3 separate tables. He wants them added to just one.

We will need a full inventory system, and the plan is to store the consumed materials for each line item and the acquired materials in a separate table. Inventory will be based on the calculation, total consumed – total acquired.

Your insight and direction would be appreciated. I have attached images of the current relationships for help in describing the situation. Sorry if this was to much information, just trying to get you all the information you might need. Our needs are definately not standard with the custom product line we offer.

We would appreciate your insight.

Thank you for your time,
Scott Holmes

The 3 Tables in question...

Example of form view showing 3 seperate tables

The full relationships of database, still need inventory and payments.


RE: Structure 3 Tables for Parts or just one

Based on not being able to see any images, tables, fields, sample records, and relationships I would suggest one table.

Hook'D on Access
MS Access MVP

RE: Structure 3 Tables for Parts or just one

The less tables, the better.  Keep 'em all in one and create a field distinguishing one from another.

Think of it this way, if you need a report with all the data (or to just look at all the records at onece) one day you don't want to go to three different places.

At work now, the guy who's taking care of the backend has a different table for each and every type of dataset.  It's a mess and it's impossible to find the data.  That's the whole point of having a table, to keep everything in one place.

"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

RE: Structure 3 Tables for Parts or just one

Thanks for the input!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close