Designing Block

[Guest]

I am trying to design a parts database for mechanics to use as a quick
reference for a parts lookup tool. I am having a mental block with what
would be the best was to set up the tables and relationships. Mechanics need
to be able to search for parts by vehicle registration #, make/model,
category, part name or Part #. Any ideas would help and may get the ball of
ispiration rolling.

 

[Jeff Boyce]

Ron

A suggestion for designing a relational database (MS Access) is to step back
from the computer and use paper/pencil to sketch out the things you'll keep
data about (entities) and the ways they are connected (relationships). The
trick is that there is no one size fits all -- your situation may not match
any other, so you need to consider what's going on in your world that you
want to model in the database.

For example, from your post, you have vehicles, parts, ?! and maybe valid
combinations of parts and vehicles (I might try to stick a Ford headlight
bezel on a Morris Mini, but someone who knows what they're doing
wouldn't...<g>).

How you subsequently create forms and queries and reports against the data
is topic for another day -- first you need to get your data modeled.

 

[Guest]

Here are some tables I came up with. Do these seem workable?

Vehicle RegNo tbl:
RegNo - primary Key
Make/ModelID
Remarks

Models tbl:
Make/ModelID - primary key
Make/Model
Manufacturer

Categories tbl:
CategoryID - primary key
Category Name
Description

Parts tbl:
PartID - primary key
Part Name
Part Description
CategoryID
SupplierID
PartNo
NSN
Make/ModelID
UnitPrice
Remarks

Suppliers tbl:
SupplierID - primary key
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Phone
Fax
HomePage

Thank for any assistance.
--
Aloha,
Ron A.

Ron

A suggestion for designing a relational database (MS Access) is to step back
from the computer and use paper/pencil to sketch out the things you'll keep
data about (entities) and the ways they are connected (relationships). The
trick is that there is no one size fits all -- your situation may not match
any other, so you need to consider what's going on in your world that you
want to model in the database.

For example, from your post, you have vehicles, parts, ?! and maybe valid
combinations of parts and vehicles (I might try to stick a Ford headlight
bezel on a Morris Mini, but someone who knows what they're doing
wouldn't...<g>).

How you subsequently create forms and queries and reports against the data
is topic for another day -- first you need to get your data modeled.

 

[Vincent Johns]

Here are some tables I came up with. Do these seem workable?

I have some suggestions...

Vehicle RegNo tbl:
RegNo - primary Key
Make/ModelID
Remarks

Since each record in the above Table is evidently intended to describe a
specific vehicle, you might call the Table [Vehicle]. Even though the
primary key matches a registration number (I assume), I think I'd call
that field [VehicleID] both here (primary key) and in other Tables that
refer to it (foreign keys in those Tables), to make it more obvious
where the definition of the key is located ([Vehicle] Table).

The foreign key [Make/ModelID] here suggests that several [Vehicle
RegNo] vehicles belong to the same [Models], and that seems correct.

Models tbl:
Make/ModelID - primary key
Make/Model
Manufacturer

I'd call the primary key [ModelsID], and I'd probably split the second
field into two parts, [Make] and [Model].

Categories tbl:
CategoryID - primary key
Category Name
Description

It's unclear to me what you'd use this for; it helps to use suggestive
names. If it's obvious to you & others what a "category" is, then I
have no objection to this name. But it wouldn't hurt to document
somewhere what it is, maybe with examples of field values, such as
"fasteners".

Parts tbl:
PartID - primary key
Part Name
Part Description
CategoryID
SupplierID
PartNo
NSN
Make/ModelID
UnitPrice
Remarks

This looks good -- a [Categories] can contain several [Parts]; a
[Suppliers] supplies several [Parts]; and a [Models] includes several
[Parts].

Suppliers tbl:
SupplierID - primary key
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Phone
Fax
HomePage

Thank for any assistance.

I was looking mainly at the links among your Tables. The other fields
you may or may not need, depending on what you intend to do with the
database. For example, [Suppliers].[PostalCode] would be useful if you
plan to send letters or packages to a supplier, but maybe not otherwise,
and you don't want to waste energy on maintaining information you don't
plan to use.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

The catagories are for the parts. Say... a mechanic needs to look up a
generic part that is not specific to a registration # or a model, like a
light bulb. He then would search in the "Lighting" catagory. Here is a
sample of info that will be used to create the parts database:

Vehicle registration number
Vehicle make, model and MFG
Part category (lighting, electrical, hydraulic, etc...)
Part info (name, part number, NSN, cost, etc...)
Supplier info

The intent of the database is to become a tool for mechanics and material
control personnel to use for quick lookup to eliminate the need for
researching the same parts that are ordered on a regular basis. Any help is
greatly appreciated.
--
Aloha,
Ron A.

Here are some tables I came up with. Do these seem workable?

I have some suggestions...

Vehicle RegNo tbl:
RegNo - primary Key
Make/ModelID
Remarks

Since each record in the above Table is evidently intended to describe a
specific vehicle, you might call the Table [Vehicle]. Even though the
primary key matches a registration number (I assume), I think I'd call
that field [VehicleID] both here (primary key) and in other Tables that
refer to it (foreign keys in those Tables), to make it more obvious
where the definition of the key is located ([Vehicle] Table).

The foreign key [Make/ModelID] here suggests that several [Vehicle
RegNo] vehicles belong to the same [Models], and that seems correct.

Models tbl:
Make/ModelID - primary key
Make/Model
Manufacturer

I'd call the primary key [ModelsID], and I'd probably split the second
field into two parts, [Make] and [Model].

Categories tbl:
CategoryID - primary key
Category Name
Description

It's unclear to me what you'd use this for; it helps to use suggestive
names. If it's obvious to you & others what a "category" is, then I
have no objection to this name. But it wouldn't hurt to document
somewhere what it is, maybe with examples of field values, such as
"fasteners".

Parts tbl:
PartID - primary key
Part Name
Part Description
CategoryID
SupplierID
PartNo
NSN
Make/ModelID
UnitPrice
Remarks

This looks good -- a [Categories] can contain several [Parts]; a
[Suppliers] supplies several [Parts]; and a [Models] includes several
[Parts].

Suppliers tbl:
SupplierID - primary key
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Phone
Fax
HomePage

Thank for any assistance.

I was looking mainly at the links among your Tables. The other fields
you may or may not need, depending on what you intend to do with the
database. For example, [Suppliers].[PostalCode] would be useful if you
plan to send letters or packages to a supplier, but maybe not otherwise,
and you don't want to waste energy on maintaining information you don't
plan to use.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.