none
Newbie - Correct Database Structure? RRS feed

  • Question

  • Hi all

    With help from this group I have made a serious start on a database which is to be used to store information about equipment that the company provides.

    The table and relationship structure is currently as follows:

    tbCustomer - 1 to many to

    tbContract - 1 to many to

    tbEquipmentItem

    For equipment that has all the same field requirements, just perhaps a different name/duty, I have used a common table.

    This all seems fine, but I have noticed that a number of the equipment items have 2 or 3 common fields e. g. coil volume, shell volume, weight etc.  So I was wondering whether my approach was right.

    I thought that as each item of equipment is an entity, that generally it should be represented by its own table, but with the duplicate fields I started to wonder (I realise that these are duplicate fields and not duplicate data).

    If anyone out there can understand the above ramblings, could you please advise whether any restructuring is in order?

    Given the amount of work already carried out, I would be inclined to leave the first phase as is and using recommendations in the second phase.

    One reservation I have is that I might end up with 100+ tables at this rate.

    Many thanks

    Phil

    Tuesday, January 5, 2016 2:05 PM

Answers

  • Is it usual/accepted practice to include "occasional" fields in a table, or is this seen as losing normalisation?

    I wouldn't say that's against Normalization rules. You can look at it this way, if you have a table of customers and you have a field for an email address, you can't help it if some (or even just one) of them does not have an email address. Right?

    Just my 2 cents...

    • Marked as answer by TheHC Thursday, January 7, 2016 10:03 AM
    Wednesday, January 6, 2016 3:56 PM

All replies

  • Hi Phil,

    Can you give us an example of your equipment data set? What is an "equipment?" Typically, it should be in one table.

    • Proposed as answer by Jim FL Thursday, January 7, 2016 2:10 PM
    • Unproposed as answer by Jim FL Thursday, January 7, 2016 2:10 PM
    Tuesday, January 5, 2016 2:51 PM
  • Equipment table would have each piece of equipment. You do not want a separate table for each item.

    Without more information, I would say this is what you want:

    Customer
    Equipment
    Contract
    ContractedEquipment

    A customer has a contract which would be for one or more equipment items like a customer order structure where an order can have many items. Each contract has contractual terms such as the Customer ID, length of time, etc. The ContractedEquipment would have a foreign key to the equipment table and the contract table.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, January 5, 2016 3:06 PM
  • Hi DB Guy

    An "equipment" is an item of process plant, e. g. a heat exchanger.  I currently have nearly 20 equipment tables, each table defining a different item of plant.  The thing is I have a lot of common fields between different equipment items.

    The table below shows a typical table (in this case for a hydroclone).

    But there are a lot of common fields such as drawing and model numbers, remarks and weight that are common to all equipment tables.

    I kind of thought that there must be problems with the normalisation exercise.

    Thanks

    Phil

    Tuesday, January 5, 2016 4:19 PM
  • Hi Phil,

    If you meant to post an image, I am not sure it came through.

    Tuesday, January 5, 2016 4:33 PM
  • That didn't work too well - trying to paste a snip of the table into the web form!

    Each table basically comprises:

    HydroID - Primary key

    ConID - Foreign key for contract table

    HYDECLItem - Item number for our reference

    HYDSONO - Order number for parent company reference

    HYDEnquiry - Enquiry number

    HYDFabricator - Builder of equipment item

    HYDDrawing - construction drawing

    HYDModel - 3D model for hydroclone

    HYDWeight - Weight of item

    Etc....

    Phil

    Tuesday, January 5, 2016 5:00 PM
  • Thanks Bill

    But each piece of equipment has its own build parameters, maybe internal tubes of differing quantities. Or perhaps there are internals requiring a drive motor.  Rightly or wrongly, this is why I have given each piece of equipment its own table.

    I think I understand what you are intending with the table structure/relationship suggested.  Yes I think this would have been a better option if the equipment we make is "static".  In fact we rarely make the same item identical twice, be it internal configuration or materials of construction or size (or all that and more) that change.  It is the detail related to each item of equipment that I am trying to track here along with the Contract and Customer that it is manufactured for.

    Does this additional info indicate that I am following the right course, or am I still barking (up the wrong tree)?

    Phil

    Tuesday, January 5, 2016 5:20 PM
  • Phil

    I see what you are saying. I still think one table will work for an equipment ID but then you would need a parts detail table to be like a pull list with an EquipID and each part ID would need a quantity, size, etc.

    I know this isn't an exact answer but maybe it will help you get started. 


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, January 5, 2016 5:37 PM
  • Hi Phil,

    There could be several approaches to this and still follow normalization rules, but creating separate tables is probably not one of them. If I understand it correctly, the situation is somewhat like this:

    A company creates custom products, and each product will have different customer specs. So, if there were 100 customers with each requiring a unique product, I don't think 100 separate tables would be the answer.

    Instead, and I think this is what Bill was suggesting, create a single table to store the ProductID and a child table to store the specs for each product. For instance, it might look something like this:

    tblProducts
    ProductID
    ProductName

    tblProductSpecs
    SpecID
    ProductID
    SpecName
    SpecValue

    Hope that makes sense...

    Tuesday, January 5, 2016 5:55 PM
  • Hi Bill and DB Guy

    Thanks for your continued interest.

    Having read your suggestions, I suspect that my explanation of the situation hasn't been crystal clear.  I have a table for each item of equipment rather than each customer's item of equipment.  So with each new customer/contract (aka order), a new record is added to the table for a given piece of equipment which details the changes in construction for that particular customer order.  I am not trying to control sub-parts of the equipment items, just design parameters.

    Looking at your tblProducts and tblProductSpecs layout, this does look like an improvement.  Perhaps adding the common-to-all fields into the tblProducts and using tblProductSpecs to control the item-specific fields.

    One last dumb question on this topic if I may?

    Is it usual/accepted practice to include "occasional" fields in a table, or is this seen as losing normalisation?

    For example, some equipment items have say internal tubes while others don't.  So I was thinking that I could use a common table for both, but simply not reference the "has tubes" field in the forms and queries for items that don't require them.

    This approach could have drastically reduced the table count.

    Thanks again

    Phil

    Wednesday, January 6, 2016 9:15 AM
  • It would seem that when you add an Order, you would add that to an Orders table setup. In general, this would be an Order Header (i.e. the Customer, Order#, Date, etc), along with an Order Detail table (the items included on the Order). 

    So if CustomerA orders ItemX, then I'd add that to my Orders setup. If this is just something I pull from stock and ship, then all I'd do is create my Packing List and Invoice, and ship it to the customer, and be done with it.

    However, if I must customize ItemX to the Customer's requirements, then typically I'd have a "Job" table, or something along those lines. The Job table would be used to store the specifics of the Customers Job, and could be related to the Orders table (if that makes sense to your setup). 

    But we don't really know enough about your business to know if this is the right path or not, so please feel free to correct my misunderstandings as needed.





    Wednesday, January 6, 2016 10:39 AM
  • Is it usual/accepted practice to include "occasional" fields in a table, or is this seen as losing normalisation?

    I wouldn't say that's against Normalization rules. You can look at it this way, if you have a table of customers and you have a field for an email address, you can't help it if some (or even just one) of them does not have an email address. Right?

    Just my 2 cents...

    • Marked as answer by TheHC Thursday, January 7, 2016 10:03 AM
    Wednesday, January 6, 2016 3:56 PM
  • Many thanks guys

    From your responses, I do not feel that my structure is fundamentally flawed, but that I could have done some serious streamlining to my table setup.

    Your assistance is greatly appreciated.

    Phil

    Thursday, January 7, 2016 10:08 AM
  • It sounds like what you are describing can be modeled as an Entity with sub entities.

    This can be physically implemented 3 different ways.

    1. You can have one table that includes all fields. The fields specific to the sub entities will be sparsely populated.
    2. You can have one table for the super entity and a table for each sub entity. The table for the super entity will contain the fields that are common to all sub entities, the tables for each sub entity will only include the fields that only apply for that sub category.
    3. You can have a table for each sub entity. Each table would include all of the common fields and the fields specific to the sub category.

    Hopefully my attachment made it through.


    Jim

    Thursday, January 7, 2016 2:19 PM
  • Hi Jim

    Thanks for the clarification.  Yes I got your image in an alert email as well as via the forum.  I think I had just about understood the options, but your post was rather more elegant than the version inside my head!

    Currently I am using solution 3.

    I suspect that solution 2 would end up with a similar number of tables, except that the sub entity tables would contain rather less fields.

    In retrospect I think I should have gone for a hybrid of version 1, using maybe 5 tables each of which align closely with say 4 equipment items.  This would reduce the table count but also not result in loads of unused fields.

    Many thanks

    Phil

    Thursday, January 7, 2016 2:42 PM
  • Hi Phil. Good luck with your project. Please let us know how it goes...
    Thursday, January 7, 2016 4:13 PM