UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Electronics Manafacturing Db Structure, Access 2016    
 
   
Stu880
post Sep 30 2019, 06:12 AM
Post#1



Posts: 4
Joined: 30-September 19



I am trying to create a data base for a electronics company and and am having trouble withe the normalization processes i should use. Each production run produces about 13,000 devices so had intailly thought i needed a table for each run but after some reading that seems like a recipe for disaster. any help would be appreciated.

current relationship structure
Attached File  Capture.PNG ( 20.06K )Number of downloads: 28


Attached File  Capture1.PNG ( 73.14K )Number of downloads: 12
Go to the top of the page
 
nvogel
post Sep 30 2019, 01:17 PM
Post#2



Posts: 1,040
Joined: 26-January 14
From: London, UK


I expect you are right. I can't imagine having a separate table for each run. Presumably the run identifier should be an attribute in one or more of your tables. Do you have a specific question?

Have you considered using a SQL DBMS instead of an Access database (ACE)? 13000 devices per run sounds like something that could benefit from a more scalable, robust platform. You could still use Access for the UI of course.
Go to the top of the page
 
Jeff B.
post Sep 30 2019, 03:30 PM
Post#3


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


I'm with NVogel … if each production run differs by a date/time span and any other characteristics, you could first create a [ProductionRun] table that lists the relevant information about each run, and include something like a [ProductionRunID]. I'd probably use an Access Autonumber field to create unique IDs.

Then you could create a table that lists [ItemProduced] (i.e., the 'devices'). Are the devices all the same (e.g., hearing aid), or does each run produce multiple devices, or does each run produce a different device? If each device produced has a unique identifier (e.g., a serial number), you could list them all. If each device produced is 'identical', what business need do you have for tracking each item (and how do you keep them separate/identified)?

More info, please...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Stu880
post Oct 2 2019, 03:14 AM
Post#4



Posts: 4
Joined: 30-September 19



Each production run (wafer) can create 100s of different types of device ( small parameter changes for research and development).
Almost every wafer design is only made a few times and each device has a 4 character identifier e.g 01A1 the 101st device on the 1st row.

I need to keep track of them so if later on i can go back and be able to know what chips are of what design and how many of them are left.
Go to the top of the page
 
Jeff B.
post Oct 2 2019, 08:44 AM
Post#5


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


Let's see if I can paraphrase …

You have chips … or 'devices'. Multiple chips/devices are produced on a single wafer. Multiple wafers are generated in a single production run.

One particular chip/device has a type (i.e., what type of device is it), a location on a wafer (?your 01A1), a wafer (which one generated in the run), and a production run.

If that's a reasonably accurate paraphrase, then using all those facts (type, location, wafer #/ID, production run) gives a way to uniquely identify each device.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Stu880
post Oct 2 2019, 10:40 AM
Post#6



Posts: 4
Joined: 30-September 19



Yes that's correct. So if i create a table (type, location, wafer #/ID, production run) that contains every device i produce, the primary key for that table should be an auto-gen number ?
This post has been edited by Stu880: Oct 2 2019, 10:42 AM
Go to the top of the page
 
Jeff B.
post Oct 2 2019, 02:20 PM
Post#7


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


Oooh, now you've opened a can of worms.

Some folks here will suggest/insist/argue that if you have 'natural' data that can be concatenated (i.e., use multiple fields as unique identifier), then that's what you "should" use.

Other folks here will suggest/insist/argue that using a single field, "Autonumber" data type, will give you a unique row identifier and you can still use an index to ensure unique combinations of those 'natural' data elements (and that's what you "should" use).

… and still other folks will point out that the discussion verges on 'holy war' …

Both approaches work. I typically use Autonumber (mainly because it is easier then to use that autonumber as a foreign key in a related table).

Good luck!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Stu880
post Oct 3 2019, 03:04 AM
Post#8



Posts: 4
Joined: 30-September 19



Thanks for the help, much appreciated!
Go to the top of the page
 
tina t
post Oct 3 2019, 03:13 PM
Post#9



Posts: 6,182
Joined: 11-November 10
From: SoCal, USA


a few notes on your posted tables/relationships: you're duplicating fields, as in

tblCustomers
Customer (since this is the primary key field, i'd suggest calling it CustomerID. it will work with its' current name, though.)

tblCustomers.Customer 1:N tblProjects.Customer

tblProjects
Project (ditto above re ProjectID; suggested, not "required")
Customer (foreign key from tblCustomers)
Owner

tblProjects.Project 1:N tblMask_sets.Project

tblMask_sets
Mask Set (strongly urge you to remove the space in this fieldname)(and again, ditto above re MaskSetID)
Project (foreign key from tblProjects)
Customer
Owner

here's where you start going off the rails, hon. each mask set record is related to a customer and owner via the foreign key field Project, that points back to tblProjects. so fields Customer and Owner are redundant and should be removed from tblMask_sets.

ditto the Customer, Project , and Owner fields in tblWaferID should be removed. each [Wafer ID] record is related to a customer, project and owner via the foreign key field [Mask Set] that points back to tblMask_sets.

and i really do urge you to remove all spaces from your fieldnames. they're just as problematic as spaces in table names, which i see that you took care to avoid. so take that extra step for fieldnames, too. my guiding rule is: don't use spaces or special characters in the name of anything that you name in a database, though the underscore character can be tolerated. that includes anything that might be named for you, if you use a wizard to build tables or forms, reports, etc.

hth
tina
This post has been edited by tina t: Oct 3 2019, 03:14 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 09:38 PM