Efficient Design?

[Brenda]

I hope my question isn't too confusing. I have many tables belonging to 3
inventory categories (Computer, Hard Drive, and Monitor). While Comp & HD
categories share designations (Status, Brand), Comp & Monitor shares others
(Recycler, Donated) Initially the inherited database design held 3 separate
relationships in one window. Realizing that I will need reports showing
results for one category/designations and at other times needing a report
showing 2 or 3 categories/designations at once, I joined all tables. Before
committing to this, I'm wondering which design might be most efficient-
seperate categoried tables with their related designations or one large
relationship including all categoried tables with related designations? I
have 14 tables.

tblComputers tblHardDrive
tblMonitor

 

[Jeff Boyce]

Brenda

I'm not completely clear on your situation...

It sounds like you are saying that your Computers&HD can have [Brand], but
your Computers&Monitor have [Donated]. Wouldn't a monitor have a [Brand]
too?

You mention 14 tables but only listed 3. What are you storing in the other
11?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Brenda

By the way, "efficient" can mean different things to different folks. What
are you meaning by it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Hello Brenda,

To say the same thing as Jeff but from a different angle with maybee a bit
more guidance on what info is needed, on DB structure questions ( which this
one probably is, even though you post indicates that you probably didn't
think so) you really need to describe the information and process that you
are trying to database and, in user tersm, where you are trying to end up.
Describing ideas of handling the secondary later stages is of little use
and is usually not worth describing. You inadvertently sort of did the
opposite of the above.

Hope this helps a little

Fred

 

[Brenda]

Jeff here are my tables:

tblComputers:
JobID
StatusID
ReceivedDate
BrandID

tblComputersRecycled:
SerialNumber-Comp
RecyclerID
DispositionDte
InvoiceNumber

tblComputersDonated:
SerialNumber-Comp
RecipientID
ProcessorID
DispositionDate
O/S
S/W
InvoiceNumber

tblHardDrives:
JobID
SerialNumber-HD
StatusID
ReceivedDate
BrandID

tblHardDrives-Recycle:
SerialNumber-HD
RecyclerID
DispositionDate
InvoiceNumber

tblHardDrive-Reuse:
SerialNumber-HD
SerialNumber-Comp
DispositionDate

(this table is designated as a lookup for Computer, HD, and hopefully for
Monitors-CRT tables)
tblStatus:
StatusID
Status

(this table is designated as a lookup for Computer & HD tables)
tblBrand:
BrandID
Brand

(this table is designated as a lookup for ComputersDonated and hopefully for
Monitors-CRT-Donated)
tblRecipients:
RecipientID
RecipientName

(this table is designated as a lookup for ComputersDonated)
tblProcessorType:
ProcessorID
ProcessorType

(this table is designated as a lookup for ComputersRecycled &
HardDrivesRecycled & hopefully for Monitors-CRT-Recycled)
tblRecyclers:
RecyclerID
Recycler

(I hope to incorporate these Monitor tables into the relationship via a
lookup to Recyclers.)

tblMonitors-CRT:
JobID
SerialNumber-CRT
StatusID
DateRecvd

tblMonitors-CRT-Recycled:
SerialNumber-CRT
RecyclerID (will use tblRecyclers as lookup)
DispositionDate
InvoiceNumber

tblMonitors-CRT-Donated:
SerialNumber-CRT
RecipientID (will use tblRecipient as lookup)
DispositionDate
InvoiceNumber

What I'm realizing is that the Computers, HardDrive, and Monitors tables do
not appear to be directly related. This looks like a spiderweb at this point.
Will this actually work?

What I meant by my usage of 'efficiency' was my goal is to achieve the
simplest design possible.

The reason for not incorporating 'Brand' into the Monitor table is because
the Dept. does not process & track it by Brand.

I hope my description helps!

 

[Jeff Boyce]

Brenda

Thanks for the clarification.

I noticed that your tblComputers doesn't have a field to record
[SerialNumber], but your tblComputersRecycled does. ... ditto for
tblComputersDonated. If you don't record the serial number when you receive
it, how do you track the difference between when a computer is received and
when it is Recycled? ... or Donated?

I'm going to guess that this set of data started out as ... a spreadsheet!
Keeping separate lists of computers, computers recycled, computers donated,
hard-drives, HDs recycled, ... is pretty much how you'd have to do it if you
were limited to using a spreadsheet.

Access is a relational database, not a "spreadsheet on steroids". If you
want to get the best (and easiest) use of Access' relationally-oriented
features/functions, you can't feed it 'sheet data.

If the terms "normalization" and "relational" aren't familiar, and if you
want to get better use out of Access, spend some time researching these and
getting a handle on how your Access table structure is NOT like a
spreadsheet.

For example, by embedding data about the process in your table names (e.g.,
XXXX-Donated, YYYY-Recycled) you are making yourself (and Access) work much
harder than you need to. "Received", "Donated", "Recycled" and their
respective dates-of-action could be looked at as indicators of current
status. Or, if you are tracking a history of those, you could use a table
something like:

tblSomeTable
SomeTableID
EquipmentID (a foreign key pointing to a computer, a hard-drive, a
monitor, a printer, a ... in your tblEquipment)
ActionID (a foreign key pointing to an action in your tlkpAction)
ActionDate
(this is only an example, and may not fit your situation...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brenda]

Fred, thanks for your guidance. I want to track our inventory process where
components such as computers, harddrives, and monitors are received,
recycled, and donated. I want to be able to create a record for each
component as it enters the warehouse. First, the serialnumber is recorded
along with it's Status 'Received'. After the technician rebuilds the
component, I'd like to have that particular record updated to reflect it's
new status as 'Recycled'. After determining which recipient it will go to,
the record's Status should be updated once again to 'donated'.
I hope I've understood and that the process I've described makes a little
sense. If not I can elaborate further.

 

[Fred]

That's great info (your 2 posts to Jeff and myself)

I'm assuming that you handle hard drives as a separate item, not as merely
an attribute / internal part of a computer.

My gut feel is that this would be best be done by one main table plus a few
ancillary and lookup tables.

The main table would have a record for each instance of one of these items
coming into your possession. And then make fields (each of which applies to
all of them) which record the distinctions and information which you
describe. Here are some example of such fields:

TypeOfItem ("Computer", "Hard Drive" "Monitor") with lookup table to help
populate it. Do the same for other fields with short lists of
possibilities.

DispositionType "Recycled" "Donated
ReceivedDate
DispositionDate
ReceivedFrom (with type in the donor, or, if they are used repetitively or
you track info on them, make a separate donor table, PK = DonorIDNumber
instead put the DonorIdNumber in your main table. Then link on
DonorIdNumber. Use thiws same scrutinity /idea for the next field.
GivenTo Name of recycler or recipient that it went to
Brand
SerialNumber
MoreDescription Longer text field with attributes that are unique to the
type of unit. E.G. processor type on computers. Hard core structure folks
might turn this into a "special attributes" table, but I would advise against
it.
You could have one or two "status" fields with Yes/No content like
"Received" "Gone" or "HaveInHouse" Strictly speaking thes could be inferred
from the above fields, but this simplified redundancy could be helpful or a
good safety net.



I think that this would provide a solid foundation for rather easily doing
everything that you described.

Hope this might be helpful.

Sincerely,

Fred

 

[Brenda]

Jeff, thanks for those points and catching the omission of the computer's
serial number- I wondered about designing from that perspective but wasn't so
sure about it- now I know. By the way the data did come from a spreadsheet!
Thanks again!

Brenda

Thanks for the clarification.

I noticed that your tblComputers doesn't have a field to record
[SerialNumber], but your tblComputersRecycled does. ... ditto for
tblComputersDonated. If you don't record the serial number when you receive
it, how do you track the difference between when a computer is received and
when it is Recycled? ... or Donated?

I'm going to guess that this set of data started out as ... a spreadsheet!
Keeping separate lists of computers, computers recycled, computers donated,
hard-drives, HDs recycled, ... is pretty much how you'd have to do it if you
were limited to using a spreadsheet.

Access is a relational database, not a "spreadsheet on steroids". If you
want to get the best (and easiest) use of Access' relationally-oriented
features/functions, you can't feed it 'sheet data.

If the terms "normalization" and "relational" aren't familiar, and if you
want to get better use out of Access, spend some time researching these and
getting a handle on how your Access table structure is NOT like a
spreadsheet.

For example, by embedding data about the process in your table names (e.g.,
XXXX-Donated, YYYY-Recycled) you are making yourself (and Access) work much
harder than you need to. "Received", "Donated", "Recycled" and their
respective dates-of-action could be looked at as indicators of current
status. Or, if you are tracking a history of those, you could use a table
something like:

tblSomeTable
SomeTableID
EquipmentID (a foreign key pointing to a computer, a hard-drive, a
monitor, a printer, a ... in your tblEquipment)
ActionID (a foreign key pointing to an action in your tlkpAction)
ActionDate
(this is only an example, and may not fit your situation...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff here are my tables:

tblComputers:
JobID
StatusID
ReceivedDate
BrandID

tblComputersRecycled:
SerialNumber-Comp
RecyclerID
DispositionDte
InvoiceNumber

tblComputersDonated:
SerialNumber-Comp
RecipientID
ProcessorID
DispositionDate
O/S
S/W
InvoiceNumber

tblHardDrives:
JobID
SerialNumber-HD
StatusID
ReceivedDate
BrandID

tblHardDrives-Recycle:
SerialNumber-HD
RecyclerID
DispositionDate
InvoiceNumber

tblHardDrive-Reuse:
SerialNumber-HD
SerialNumber-Comp
DispositionDate

(this table is designated as a lookup for Computer, HD, and hopefully for
Monitors-CRT tables)
tblStatus:
StatusID
Status

(this table is designated as a lookup for Computer & HD tables)
tblBrand:
BrandID
Brand

(this table is designated as a lookup for ComputersDonated and hopefully
for
Monitors-CRT-Donated)
tblRecipients:
RecipientID
RecipientName

(this table is designated as a lookup for ComputersDonated)
tblProcessorType:
ProcessorID
ProcessorType

(this table is designated as a lookup for ComputersRecycled &
HardDrivesRecycled & hopefully for Monitors-CRT-Recycled)
tblRecyclers:
RecyclerID
Recycler

(I hope to incorporate these Monitor tables into the relationship via a
lookup to Recyclers.)

tblMonitors-CRT:
JobID
SerialNumber-CRT
StatusID
DateRecvd

tblMonitors-CRT-Recycled:
SerialNumber-CRT
RecyclerID (will use tblRecyclers as lookup)
DispositionDate
InvoiceNumber

tblMonitors-CRT-Donated:
SerialNumber-CRT
RecipientID (will use tblRecipient as lookup)
DispositionDate
InvoiceNumber

What I'm realizing is that the Computers, HardDrive, and Monitors tables
do
not appear to be directly related. This looks like a spiderweb at this
point.
Will this actually work?

What I meant by my usage of 'efficiency' was my goal is to achieve the
simplest design possible.

The reason for not incorporating 'Brand' into the Monitor table is because
the Dept. does not process & track it by Brand.

I hope my description helps!

 

[Brenda]

But that's so simple! I had a feeling there was a better way but didn't know
what it was. (I do handle the hard drives as a seperate item.)

Fred and Jeff, thanks for taking the time to process all of my information
and provide direction. Tremendous!

Sincerely,
Brenda