Re: Basic Database Design

Tech-Archive recommends: Fix windows errors by optimizing your registry

Chris Thanks again.
I will try to do the design that you have send, and will "post" you a note in
order to tell you if it worked. OK
Regards
Silvex


Chris2 wrote:
<snip>

I adress this in this group because I think is a question of
Tables/Forms/queries so Database Design
Sorry if I'm wrong.
regards
Silvex

Silvex,

You're not wrong to post here. Forms questions are posted here, and get answered here,
all the time.

I was just making a recommendation.

As for the table designs, the first and third look ok.

As for the second, tblProducts, InsuranceID needs to be split out into a new table.

ProductInsurance
ProductInsuranceID -- PK
ProductID -- FK to Products (ProductID) -----\
InsuranceID -- FK to Insurance (InsuranceID) -\
ProductInsuranceDate -------------------------- 3-column unique index.

As for the fourth, tblClient, it combines client data with product, category, and
Insurance data all at the same time.

Category information is already contained in tblCategory and tblProduct

You need three separate tables, Clients, ClientProducts , and ClientInsurance.

Clients
ClientID -- PK
ClientNamePrefix
ClientNameFirst
ClientNameMiddle
ClientNameLast
ClientNameSuffix

ClientProducts
ClientProductsID -- PK
ClientID --- FK to Clients (ClientID) --\
ProductID -- FK to Products (ProductID) -\
SaleDate --------------------------------- 3-column unique index.

ClientInsurance
ClientInsuranceID -- PK
ClientID --- FK to Clients (ClientID) -------\
InsuranceID -- FK to Insurance (InsuranceID) -\
InsuranceDate --------------------------------- 3-column unique index.

I personally would use InsuranceStart and InsuranceEnd, so I would know how long they were
insured under a particular insurance. (And probably two dates for ProductInsurance, as
well.)

Oh, I just noticed I stopped using the "tbl" prefixes in the middle of writing that.

Also, you have the UnitPrice column in both tblProduct (where it appears to belong), and
in tblClient (where it does not belong, as UnitPrice does not describe a client). I would
also add at least one date column to tblProduct, so you can track when prices change.

Sincerely,

Chris O.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200707/1

.