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.
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.
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).
If you have 4 tables A,B,C,D
then the links are between
A-B, A-C, A-D
So with 4 tables you need 3+2+1 link tables.
With 33 tables you need 32+31+30+29+...+1 link tables
Seems like quite a lot of work to me
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.
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)
- 1 user table, (User can work for one or more repair center)
- 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
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:
columns: user_id, repaircenter_id
(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?)
Originally posted by georgev
P.s. Why is everything varchar(20) ?!
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?
Originally posted by r937
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
Nope - I'll keep to the question at hand. Anyway bye again
Originally posted by Gary R. Schaecher
We are not interested in any Entity-Attribute-Value model. We are getting rid of anything that resembles EAV in our current schema!
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".
Originally posted by Gary R. Schaecher
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.
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.