April 28, 2011 at 9:42 am
I have a database with a few tables that allow a user to select from values that we have entered or select from a list of values that they have entered. For example, we have a table with about 12,000 organizations. A user can select from those organizations to identify them as their current or previous employers. Because obviously we would not be able to have every organization in the world listed, if a user cannot find the organization they are looking for, they can enter one.
My question is, what is the best way to store both sets of data so that it will be easily used in different situations? Right now for this example, there is our table "organization", "user_to_organization" to link the users to our organization, and then "user_organization" to list any custom organization with a FK to the user. This makes the most sense on the surface, but there is one situation (but there could easily be more come up) where a user can link a recrod in another table to one of their organizations. This separate table now requires two columns ("organization_id" and "user_organization_id"). This seems clunky to me.
In another set of tables that allow for "custom" records in addition to values we supply, the table that joins the user to our values also contains additional fields to store the custom values. For example, there's our table "equipment" and "user_to_equipment" to link our equipment to the user. Then, if the "equipment_id" FK is NULL (or a value defined to represent custom equipment in general), the values in extra fields are used ("equipment_name", "manufacturer", etc.). In this particular case, I am actually planning on moving those extra fields into an XML column to hopefully make it a little more scalable. The biggest advantage I see to this method is that you don't have to have duplicate columns in each table that references a user's equipment. The downside is that you have "optional" columns (or at least an XML column) that are used maybe 5% of the time.
Whatever I choose, I'd like to use the same method for any custom records.
April 30, 2011 at 8:31 am
Do you have an ERD?
Hopefully I'm wrong but the design of the Database that you are working with as described seems a little ugly.
Could you provide the tables, attributes, relationships and dummy data so that we can analyze and better assist you?
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 30, 2011 at 8:57 am
Another issue you need to consider:
How are you going to prevent a user adding a company "My company", if there is already a company named "My company Inc." together with "My company Inc" (without the dot at the end)?
April 30, 2011 at 9:24 am
Nice catch on the Company Name Issue.
The FEIN (Federal Employer Identification Number) which is equivalent to the SSN uniquely identifies a Company. That is the best approach to identify the Company provided that you can obtain that information.
If you work for a government Entity you can obtain a Company FEIN Table and perform a lookup.
To avoid duplication ideally you should have a Company Table with a FIEN Number as the Primary Key.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 30, 2011 at 10:51 am
The organizations that a user can have in their profile could be anything from a major company to a company they ran out of their garage. It isn't huge deal if there are duplicates, especially since the custom organizations are in a separate table.
In the other example of equipment, it's the same situation where our list should contain all official equipment, but if a user wanted to enter something they invented, they could do that. Only equipment that a user has selected from our list is able to be seen by other users, so the custom equipment (like the custom organizations) is purely for the user's own record keeping.
Here is an example of how the organizations are currently set up.
user - Users
user_id INT PK
user_name NVARCHAR
user_id, user_name
1, 'Fred'
2, 'Steve'
3, 'Mary'
organization - Organizations that we have loaded from a government listing
organization_id INT PK
organization_name NVARCHAR
organization_id, organization_name
1, 'Walmart'
2, 'Target'
3, 'Goodwill'
user_to_organization - Organizations that users have selected from our list
user_to_organization_id INT PK
user_id INT FK
organization_id INT FK
user_to_organization_id, user_id, organization_id
1, 1, 2 - Fred works for Target
2, 1, 3 - Fred works for Goodwill
3, 2, 1 - Steve works for Walmart
4, 3, 2 - Mary works for Target
user_custom_organization - Organizations that a user has entered
user_custom_organization_id INT PK
user_id INT FK
organization_name NVARCHAR
user_custom_organization_id, user_id, organization_name
1, 3, 'Mary's Hair Salon' - Mary has a hair salon
2, 2, 'Steve's Motorcycle Shop' - Steve has a motorcycle shop
record - A record (maybe a certificate) entered by the user and associated with an organization
record_id INT PK
user_id INT FK
organization_id INT FK
user_custom_organization_id INT FK
record_information
record_id, user_id, organization_id, user_custom_organization_id, record_information
1, 1, 2, NULL, 'Received training at Target' - Fred received training at Target
2, 3, 2, NULL, 'Got promoted at Target' - Mary got promoted at Target
3, 2, NULL, 2, 'Got safety certified' - Steve got safety certified at his motorcycle shop
With equipment, we wanted to make it so that if a user enters custom equipment and enters information associated with that equipment, they could "convert" it to equipment that is in our system easily if they realize it's already there, or if they request it to be in our system and we add it. I would like that flexibility with the organizations as well, so that we could address issues like "My Company" vs "My Company Inc."
Here is what I was thinking.
The organizations that a user can have in their profile could be anything from a major company to a company they ran out of their garage. It isn't huge deal if there are duplicates, especially since the custom organizations are in a separate table.
In the other example of equipment, it's the same situation where our list should contain all official equipment, but if a user wanted to enter something they invented, they could do that. Only equipment that a user has selected from our list is able to be seen by other users, so the custom equipment (like the custom organizations) is purely for the user's own record keeping.
Here is an example of how the organizations are currently set up.
user - Users
user_id INT PK
user_name NVARCHAR
user_id, user_name
1, 'Fred'
2, 'Steve'
3, 'Mary'
organization - Organizations that we have loaded from a government listing
organization_id INT PK
organization_name NVARCHAR
organization_id, organization_name
1, 'Walmart'
2, 'Target'
3, 'Goodwill'
user_to_organization - Organizations that users have selected from our list
user_to_organization_id INT PK
user_id INT FK
organization_id INT FK - Set to NULL or an ID to represent that it is custom
organization_information - Only recorded if it is custom
user_to_organization_id, user_id, organization_id, organization_information
1, 1, 2, NULL - Fred works for Target
2, 1, 3, NULL - Fred works for Goodwill
3, 2, 1, NULL - Steve works for Walmart
4, 3, 2, NULL - Mary works for Target
5, 3, NULL, 'Mary's Hair Salon' - Mary has a hair salon
6, 2, NULL, 'Steve's Motorcycle Shop' - Steve has a motorcycle shop
record - A record (maybe a certificate) entered by the user and associated with an organization
record_id INT PK
user_id INT FK
user_to_organization_id INT FK
record_information
record_id, user_id, user_to_organization_id, record_information
1, 1, 1, 'Received training at Target' - Fred received training at Target
2, 3, 4, 'Got promoted at Target' - Mary got promoted at Target
3, 2, 6, 'Got safety certified' - Steve got safety certified at his motorcycle shop
Two benefits of this are: Instead of having organization_id and user_organization_id in the record, you just have one field. If a user enters a custom organization, associates records with that organization and then finds it in our system, they would just have to select the correct organization to update the user_to_organization record.
The downside that I'm unsure of is that the organization_information is only stored for custom organization (probably a small portion of the organization records).
I hope that helps clear up the situation.
Thanks.
April 30, 2011 at 11:20 am
user_to_organization - Organizations that users have selected from our list -
user_id INT PK
organization_id INT PK
Do not use a surrogate key, use a composite key User_ID & Organization ID
The User_ID is a FK in the Users Table Table relating to this table
The Organization_ID is a FK in the Organizations Table relating to this table
user_custom_organization What is the difference between the user_to_organization table and the user custom_organization table? You may need to consolidate the User_to_organization tables, and add a column to identify whether it is a standard association or a custom one. Depending upon the business requirements you may need another association table.
record - A record (maybe a certificate) entered by the user and associated with an organization.
I’m not sure what you mean by may be a certificate? This could be a problem?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 30, 2011 at 12:10 pm
In the first (current) example, the user_to_organization table is just a link from the users to the organizations we have listed. The user_custom_organization is just the custom organizations that the users have entered, directly linked to the users.
In the second example (the one that I was thinking about using), those two tables would be consolidated sort of like what you said. If the organization_id is set in user_to_organization, then it is in our list of organizations, otherwise the organization information is entered by the user and stored in the additional field(s). I could use another column to identify if it's a custom organization as well, but I'm not sure if that's necessary, since you would be able to tell if the organization_id is set or not.
I understand that it would make more sense to use user_id and organization_id as a composite key for records in the organization table, but custom organizations entered into user_to_organization in the second example wouldn't have an organization_id.
As for the record table, I was just using that as an example. In our system, a user can have certificates, and they can link those to one of their organizations to list where they got it from. Users can also have records in another table for training that they've received that might be from an organization in their profile.
April 30, 2011 at 12:52 pm
I agree with you that the user_to_organization and user_custom_organization tables should be consolidated
You may need a user_organization_Type Column which is stored in the user_to_organization table which is a child to a user_organization_type table.
The record table does not appear to be an entity and does not meet the criteria as a table.
Could you please go into a little more detail?
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 30, 2011 at 12:53 pm
I backed out and in and inadvertently created a duplicate post.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 2, 2011 at 8:20 am
Really, the "records" are separate from the organizations. A user can have certifications and information about training they have received, and they don't have to be associated with an organization, but if they want to show where that training or certification came from, they can list an organization.
We actually do have an "user_to_organization_type_id" that I didn't mention, although it is to represent the type of association to the user. For example, a user can categorize an organization as their current employer, previous employer, or place where they might receive training. This leads me to believe it would be appropriate to use the "user_to_organization_id" that is generated when a user adds an organization to their list (either from our system, or a custom one) as a FK in the training or certification records, because that way it's referencing the association, so a user could list the same organization twice (once as a previous employer, and once as a trainer), and then they could differentiate between the two in certifications. Then, a future employer could see (on a resume that is generated by our software) that maybe one certification was unique to the previous employer, but another is the previous employer certifying the user can do something universally.
May 2, 2011 at 8:44 am
Certifications?
Could you please define what a record is?
Do you have business requirements for this project?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 2, 2011 at 12:42 pm
A certification is just a record entered by a user. It could be that they are certified to work on a piece of equipment, or that they passed a test or something. If they want, they can list that they received that certification from a specific organization. The same with training records. It's another table where a user can enter information about training they have received. It could be online training that they took, a class that they went to, or just on-the-job training that they did with someone. Again, if they want to record where they received that training from, they can do so by linking it to an organization.
As far as business requirements for things like certifications and training records, there aren't any at the moment other than using them on a resume. It's purely a record keeping system for the user's personal information. For the organization part of it, it's just supposed to be so that the user can have a list of organizations that they can list on their resume and associate with their training records or certifications if they want to.
May 2, 2011 at 1:50 pm
It seems to me that you have an entity decision to make about what the data means before you move.
In terms of your certifications, organizations, inventions, and anything else, I think you want to go with a master table of names, and then a FK linking table that links a user_id to an organization_id (or cert_id, etc).
You can use the concept of an "owner" in the certification (lookup) table that holds the user_id if they have put it in themselves. that way you could easily check if they have the right to "edit" it. For general companies, like Wal-Mart, you could have them owned by 0.
Alternatively, you could have a "type" and store the owner there, or develop some permissions structure. If I started SQLServerCentral and Andy wants to claim work there, I could potentially approve that.
However how do you know if "Wal-Mart" is the global corporation of something that I ran out of my garage? That could be an issue, and while you can reconcile this with good tools in the application, you should plan on handling it with the database. Make sure that you allow for those entries and you have a way to filter them (flag) if a custom entry should not be displayed for others, or if you allow duplicates.
May 2, 2011 at 2:48 pm
Really, an organization wouldn't be an "owner" of a certification. That is specifically tied to the user. And the user enters the things like certifications and training records. There isn't a master list of those.
The user can just "attach" an organization name to a certification or training record. And the more I think about it, it would probably be useful to know the association of that organization anyway, so I think using the user_to_organization table to store both the links of a user to a system organization and a user's custom organization information is going to be the best. Then use the user_to_organization_id as a FK in the certification or training record tables. That way, if I worked for a company that provided training, I might have received training from them as an employee, and then after I leave, I might want to be able to identify that I also went back and received more training as a "student".
As far as knowing if I entered something like Walmart as an organization and whether or not it's valid, that doesn't matter in this part of the system. If a person did, it would be the same as putting that you worked there on your resume, but didn't. That's where validation of certification and training records comes in, but that's and entire other part of the software. :w00t:
Anyway, I think I have all I need at the moment, unless someone has a better idea. Thanks!
May 2, 2011 at 3:07 pm
Respectfully please don't underestimate the Business Requirements for it is a very important part of the System Development Cycle (SDLC).
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply