Re: Beginner help needed in database design

Ok, this is your original text:
>> I have a table of products (serial number is the primary key,
>> description and a product category). Each machine has at least 1 PCB
>> inside it, maybe more. Each PCB has a PCB_No (e.g. PCB_001, a
>> description, some software and a revision number (e.g. 1.1). Each PCB
>> can have more than 1 revision, so there could be 2 instances of
>> PCB_001, one with revision 1.0 and another with 1.1. Also, each PCB
>> can also have different software on it telling it what to do.
>>
>>

One thing strikes as odd: primary keys are a techie thingy, something most database systems need internally; it's not a design, nor a logical thing - it's the implementation.

OK - for the analysis part:
So, you have products.
And you have a relation:
Each product has one and only one serial number stamped on it. And the reverse:
Each serial number belongs to one and only one product.

Assumption:
Each serial number is unique (per product, perhaps in the universe, I don't know, but at least per product).

Now, you would map the entity product to a table products. As serial number has a one-on-one relation to product, and is unique, rework it to an attribute of product. =# This is a great candidate for an identifier column! In fact, you declared it a primary key (which is the implementation for it).
Please bear in mind, relationships are often implemented via primary/foreign key pairs. This may implement, that this field (which is the implementation of the attribute "serial number") appears in other tables as well.
This may, or may not be suitable, in which case a technical key can be implemented, using a sequence, which generates a meaningless number.

Now, it's getting misty; products are gone, in come machines, and PCB's.
Relations:
Each machine must contain one or more PCB's. Each PCB may be contained in a machine.
Each PCB must have a PCB number (is this unique?

     if so, it may be a candidate for a primary key column), a description (of the PCB, I presume)

Same drill here for software, software versions, and revision levels of PCB's. Describe all relations both ways.

Now, for the implementation part:
Make sure, every rule you have written down, is like: Each [entity] [[must|may] noun]
  [zero, one or more|one and only one|one or more] entity.

Each entity (e.g. machine) becomes a table (e.g. machines). I like the convention of keeping entity names in single, table names in plural.
Each relation pair becomes a foreign key relation. Make sure to keep a close eye for 1:1 relations - these are 'not normal', and should be inspected to see if these should not really be attributes (or fields)

Now, of course, I have not yet spoken about sub- and super types, recursive relationships, exclusive or's and domains.

BTW, your product classification can be found on pg 8-12 and 8-13 of 'Entity Relationship Modelling' by Richard Barker.

As for the other tables, I'd have:
- PCBs (with fields, same as the primary keys fields for

    Softwares and Revisions)
- Softwares
- revisions

There would be relations between PCBs and softwares, and PCBs and revisions.

Hope this helps in some way

-- 
Regards,
Frank van Bortel