Could you check my table design structure?

[addy]

Could someone please check my table design before I go ahead and start work on this database? We have a large(ish) number of dossiers which contain various documents and each dossier is held by between 1 and 18 holders. In total there are about 100 different users. I am designing a database which will record the holders details (I am thinking of using an autonumber primary key as a 'Holders ID' and then another field for their name), their locations (addresses), which dossiers they hold and the details of the dossiers (Dossier number, dossier title). The table structure I am thinking of is this:

Table: HolderID

Fields:

HolderID
HolderName

Table: HolderLocation

Fields:

HolderID
CompanyName
AddressLine1
AddressLine2
City
PostCode

Table: DossierInfo

Fields:

DossierNo
DossierTitle

Table: HoldersList

Fields:

DossierNo
HolderID

So I guess my last table will be a many-many table (if that's the right terminology) where by each dossier number will appear between 1 and 18 times and against each instance, will have a different HolderID.

Is this the best way to design my tables? Could it be done better?

Thanks.

 

[PHV]

if that's the right terminology
Others say: junction table for many-to-many relationship.

Why have 2 separate tables HolderID and HolderLocation ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnherman]

The table(s) associated with many-to-many relationships have also been called bridge tables, relation tables, and associative tables.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Remou]

I have often wished I included dates ...

 

[addy]

Why have 2 separate tables HolderID and HolderLocation ? "

Good question.

And one I don't really have an answer too.

 

[PHV]

The real questions are:
Could an holder be in many locations ?
Could a location have many holders ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[addy]

It is entirely possible that a location could have various holders because a particular company may have 4 or 5 members of staff who would be holders.

Therefore, I suppose I could add a 'CompanyCode' field to the HolderID table and then I could replace the HolderID field in the HolderLocation table with a CompanyCode field.

That way I would only have to record addresses once rather than numerous times.

 

[Crowley16]

also, could a company have many locations? and again, could each location for a company have many holders?

try to break down every piece of data in your database into it's smallest possible grouping or objects, i.e. atomise...

once you've atomised your data into object, list all possible relationships between all objects...

If 2 objects have a 1-1 relationship with each other, and NO 1-many relationships with something else, then you can put them in 1 table, but otherwise they will need seperate tables linked by a id field...

--------------------
Procrastinate Now!

 

[willir]

Addy

Not sure if you reached a successful conclusion based on the above replies.

[color]1 and 18 holders[/color]
The important thing about a holders is that there is more than one.

I assume a "holder" is someone who is responsible for the document.

Can a "holder" hold more than one dossier? I suspect yes.
Can a dosier be held by more than one holder?
If "no", then you have a one-to-many relationship.

Is it a good idea to track who has a dosier "signed out", or if a dosier is transferred from one owner to another (vacation, holder quits, better "fit", etc) If you are tracking "holders" and ownership of dosiers can be transferred, you may want to consider tracking who had what and when. If this sounds worthwhile, you can use a history table to track dates and activity.

...there are about 100 different users
Your design does not mention "users". With respect to the objectives of this database, are "users" important? Do the "users" read documents? Do you want to track which dosier and which documents are being read?

If so, you will need to add a "tblUser" table. Most likely, you will have a many-to-many relationship between users and dosiers since a reader will most like want to read a dosier more than once.

a particular company may have 4 or 5 members of staff who would be holders

As pointed out, you may want to change your design for "holder" and "location".

A typical design for a contact and their business is a one-to-many where one business will have many contacts. For a one-to-many, the foriegn key is stored on "many" side. In your case, I suspect you want the LocationID stored on the Holder table instead of storing the HolderID on the HolderLocation table.

dossiers which contain various documents

Do you want to itemize what documents are within a dosier?

If so, add tblDocuments to the design. Since there is a one-to-many realtionship between dosiers and documents, store the DosierID field as a foriegn key on the DosierInfo table.

If you want, repost your design.

Richard