Design Issue

[ddrew]

I am rebuilding a DB that I built a while ago. One of the big issues was building the tables correctly. So to that end I'm asking! Here's how it goes:
Table1: CATTSitetbl, Coloums are: CATTSiteID (Auto), SiteName, there are two records UK and Germany.

Table2: ResourceTypetbl, Coloums are: ResourceTypeID (Auto), ResourceName, CATTSiteID (relationship to CATTSitetbl, CATTSiteID) there are two records Personnel and Hardware.

My question is (and if this is answered then rest will fall into place), should the table ResourceTypetbl have four records Personnel with a CATTSiteID of 1 (UK) and Personnel with a CATTSIteID of 2 (Germany) and Hardware with a CATTSiteID of 1 (UK) and Hardware with a CATTSIteID of 2 (Germany).

I'm asking this as both sites have Personnel and Harware. Ive been given advise from other peopl, but I want to get this absoloutly right!

Thanks

 

[georgedwilkinson]

Your example makes little sense but I believe you actually will be needing a third table called a junction or associative table. It enforces what is called a many to many (aka M:M) relationship in data modeling.

In your (hopefully hypothetical) situation, you would still have 2 records in CATTSitetbl and the 2 records you mentioned for ResourceTypetbl. The assignment of sites to resource types would take place in the junction table (in my case, using my standards, it would be named something like SiteResourceTypes).