Table setup for component serial numbering system

[maximisus]

Hi, I'm new here but have made few small database's in the past, hence the reason my company have asked me to put this one together. i'm just not sure of what would be the best layout for the tables, any help would be much appreciated!

We have a drawing numbering system so that every component that is the same is assigned the same drawing number, then each separate component with the same drawing number gets a separate serial number so that we can track its use etc.
therefore an individual component is identified by: [drawing number and serial number] but i want these to be able to be entered separately!

at the moment this is recorded on paper, with a different sheet for each of the (approx 2000) different component names with the paper recording: Order No; Order Date; Drawing No (last number of drawing number can be incremented but the component keeps the same name and the serial number continue incrementing); Serial No; comments.
they would also like me to add to this record: Delivery date; Component Name; supplier; Scrap date.

I have started but have run into a road block with the layout, i'm not sure whether it would be best to have 2000 different tables for each separate component so that no info is being repeated on the same table as is good practice, or can I get away with having one table that stores all the components even though it would contain the same drawing number over and over.

If you need more info on the setup just ask, any help would be great!

Matt

 

[Mr. B]

My initial thought about the inforamtion about your components is that all you need is a table that would hold the info about each component. This would be things like Name, initial create date, and any other information that is directly related to a specific component. I might consider having a field that would hold the "NextSerialNum" value. As any component is selected and added to a drawing you can read the value from the "NextSerialNum" field and assign that number as the serial number for the component in that drawing. Then you would increment the serial number that was just used by 1 and write the new value back to the "NextSerialNum" field in the components table so it will be there for the next time that component is added to another drawing.

There may very well be other things that would cause me to change my intial thought on how to manage this data, but I with the information you have provided, I would think that you certainly do not need a table for each component.

Hope this helps.

 

[DCrake]

You only need 2 tables

Drawing Table

DrawingID:
DrawingName:
DrawingEtc...

Revisions Table

DrawingID
RevisionNumber
RevisionDate
Etc...

Then create a one to many relationship between the DrawingID in both tables.

 

[maximisus]

i'm not sure if you understand what im looking for DCrake, it's not about drawing revisions and updates, its the actual produced components from the drawings that we want to track and record but thanks anyway

thanks mr B, i'll give your idea a go and see where it gets me

Matt

 

[gemma-the-husky]

Hi, I'm new here but have made few small database's in the past, hence the reason my company have asked me to put this one together. i'm just not sure of what would be the best layout for the tables, any help would be much appreciated!

We have a drawing numbering system so that every component that is the same is assigned the same drawing number, then each separate component with the same drawing number gets a separate serial number so that we can track its use etc.
therefore an individual component is identified by: [drawing number and serial number] but i want these to be able to be entered separately!

at the moment this is recorded on paper, with a different sheet for each of the (approx 2000) different component names with the paper recording: Order No; Order Date; Drawing No (last number of drawing number can be incremented but the component keeps the same name and the serial number continue incrementing); Serial No; comments.
they would also like me to add to this record: Delivery date; Component Name; supplier; Scrap date.

I have started but have run into a road block with the layout, i'm not sure whether it would be best to have 2000 different tables for each separate component so that no info is being repeated on the same table as is good practice, or can I get away with having one table that stores all the components even though it would contain the same drawing number over and over.

If you need more info on the setup just ask, any help would be great!

Matt

your roadblock: neither

a) certainly don't have 2000 separate tables. I don't think you could anyway. I am sure there is an upper limit of 255

b) don't repeat the drawing number exactly in the way you describe. Have a table to manage the separate drawings. Have another table for the variants. Link these together by using the PK (primary key) of the drawings tables, as an FK (foreign key) in the variants table, to manage the links. To this end, I would add an extra auto-number key as PK in the drawings table - rather than using the whole drawing number.


which is what DCrake was getting it, in general terms.