I have 33 entities in a new database schema. The first 32 entities can each be related to an unlimited number of records in entity table 33.

Is it best to create one link table with the 2 foreign keys for EACH of the 32 entities, or to create one link table with 32 foreign keys (pointing to my 32 entities), plus an additional foreign key to entity table 33? I an office full of engineers split down the middle as to which is the best way.

For example: Entity A is Equipment, Entity B is a Department, Entity C is a Building and so forth. Each of these 3 types of entities can be related to many repair centers. Do we create a link table with 4 foreign keys or 3 linked tables, one for A, one for B, and one for C.

an office full of engineers, eh?

separate link tables

They are going to ask me why? Simply saying multiple won't end this debate.

But Rudy simply saying should
Ask yourself what kind of relationships these entities have with eachother... In fact, share that with us too

The only relationship is that all 32 entities require to be filtered based on the union of their join table with the join table of the user's who are attempting to access them. For example: User's with access to repair centers ABC, must only be able to work on entity records whose repair center list contains ABC.

union of their join table with the join table of the user?

If you have 33 tables where items in any table might relate to items in any other table then you're going to need a lot of link tables! I tried to work it out mathematically but couldn't think of the correct term (it's not factorial).

Couldn't you have just one link table that will link to any of the other tables. Obviously you'd have to have the type of entity along with it's id to identify a given item and all the id's would need to be stored in the same type of field.

You'd want a relationship lookup table that would say which relationships are valid and what they are called.

So your choice boils down to

1. 3 tables and a few compromises
2. or perfect formal design but potentially 100's of tables and tonnes of code supporting it.

Each to their own I guess.

Mike

PS Gary - it would make it much easier to understand the issue (for me anyway) if you describe everything in plain english.

Mike, no.
P.s. Why is everything varchar(20) ?!

come on, mike, don't hold back, go ahead and send him your "off the shelf" EAV solution

and with that, i'm out of this thread

So, let me try to recap the scenario again:

Basically, I have the following DB model to filter records from 32 tables which contain information from different facilities. A facility can have one or more repair centers. Think of a chain of hospitals (one organization with many facilities)

Tables:

- 1 user table, (User can work for one or more repair center)

- 1 RepairCenter table

- 32 other entity tables (departments, buildings, equipment, tasks, projects, hazardous material, warehouses,etc.)



Relations:

- 1 user (= 1 record) can be linked to multiple RepairCenters (= multiple records)

- 1 Repaircenter (= 1 record) can be linked to multiple users (= multiple records)

- An entity table can be linked to multiple RepairCenters (= multiple records)

- 1 RepairCenter record can be linked to multiple entity tables



Business Logic:

A user is linked to X number of repair centers. Whether or not the user may work with data in a certain entity table depends on if there is a link between that entity table and one of the RepairCenters the user is linked to.



So far correct?



This would give me the following datamodel:



Table users

columns: user_id



Table user_repaircenter

columns: user_id, repaircenter_id



Table repaircenters

columns: repaircenter_id



Table repaircenter_entity

(This is where we tying to decide if we need 32 or single 'join' table)

columns: repaircenter_id, entitytype_id (32 foreign keys if a single table?)



Table entities

columns: entitytype_id, entity_name,

I hope this clearer....

Here's a really simple solution (using department as our basis for security).
Security(UserID, Department)
Very basic, very simple.

Another method would be to use security groups. Similar to above but you have GroupID instead of userid and then you add a column to your users table called GroupID which is your related field.

EDIT: Just read your previous post - second suggestion wold be best methinks

We are not interested in any Entity-Attribute-Value model. We are getting rid of anything that resembles EAV in our current schema!

Good for you Gary!!
Anyhoo - I'll take a better look at this when I have some more time...

Mike said 'If you have 33 tables where items in any table might relate to items in any other table then you're going to need a lot of link tables!'.

This isn't true, these items are NOT related except they need to be filtered by the user's repair center list. I think r937 is correct to say to use 32 join tables.

Because I haven't been told what type/size of ids he uses. I thought it best to put something down as a guess and left it at varchar(20). I was going to put in a comment to say these types were just place holders but forgot. Perhaps with your vast experience you could think of some better way of putting the idea across?

Nope - I'll keep to the question at hand. Anyway bye again

Who proposed EAV here? I simply responded to (what I thought was) your original question your "how do I provide links between all 33 entity types".


Fair enough but your original description was very hard to decipher. I guess if you spent a little more time describing your problem then I'd of been able to provide a more relevant solution - or most likely left it alone alltogether.

Mike