These fields in one table or separate tables?

Creating tables and queries

These fields in one table or separate tables?

Postby Maax555 » Fri Mar 16, 2018 12:55 pm

Hi All, i am starting a new database as i have had a few comments on my current effort being fundamentally incorrect. While i am still working on my incorrect database in the background i wanted to get started on this new one.
I want to make sure i am starting out correctly with correctly formatted tables. So my database is basically to track and log PARTNUMBERS. i think i have created all of the tables i will require but want to check if i should have tables for any of the following fields or if they are ok to be in my main PARTNUMBERS table. So a PARTNUMBER will be for example

Part Number
Revision Level
Wire Count
BOM Cost
Labour Time
Prototype Price
Production Price
Annual Qty
Date Recieved
Date Completed

So all of the above i am considering as having fields directly in the main PARTNUMBER table (do not have own tables) entered on the PARTNUMBER-FORM
The following are other fields which will be part of the PARTNUMBER-FORM but have tables.

Q1 Does this look OK as a starting point?
Q2 Are the fields i have listed which do not have a table OK as fields directly inside the main PARTNUMBER table?
Q3 I am unsure if i should have the partnumbers created as one field or two, that is either partnumber/revision (example partnumber =12345/1 and remove the revsion level field) or partnumber and revision,(example partnumber = 12345 revision 1). This is required as we will have a part number 12345/1 then later on this will have a modification and become 12345/2. I would like to be able to run a report on partnumber without a revsion level that would list all of the various different revision levels and details for that particular partnumber.

any initial advice would be greatly appreciated.
Regards Maax
LibreOffice on Windows 7
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: These fields in one table or separate tables?

Postby keme » Fri Mar 16, 2018 3:11 pm

That is a question relating to DB normalizing, which depends on context as much as content. Some points to consider:
  • Does "part number" identify a component type, or one particular item?
  • Could there be several "replacement paths", or will the new part always replace the previous, in a "single file" fashion?
  • What about "similar"? Does it signify "optional/possible replacements", matching styles (for visual presentation/matching purposes) or something else? Could there be multiple "similar" objects for one particular part?
User avatar
Posts: 3185
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: These fields in one table or separate tables?

Postby Maax555 » Fri Mar 16, 2018 4:13 pm

Partnumber is an item. So for example the partnumber actually refers to a drawing. we would use the drawing to manufacture a component which would be saved on our system as the drawing number and revision. So our MRP system would have 12345/1 for the first revision we manufacture, then 12345/2 etc. However for the database i have had suggestions that it is better to save the partnumbers as 12345 and have a separate field for the revision level. I am unsure. It will be easy to simple have a partnumber with the revision built in such as 12345/1 but for use in a database it may be preferential to use a separate revision?
Similar is simply for reference so when we add a new part number we can also add a similar part number on the form which will show the wire count, BOM and labour so we can compare the two when we price the new partnumber.
Replaces is basically the same. we will show a replacement and similar partnumber which is already existing in the database by using a listbox which will show all partnumbers on the system related to the particular customer.
I can upload a screenshot of my previous form that shows the replace and similar fields.
Either was when we add a new partnumber its simple just a new record and will not replace or require us to delete or change any other previous record.

Regards Maax
LibreOffice on Windows 7
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests