Howdy all. I've just started working with SQL server and I'm trying to design a fairly complex (for me anyway) project. I'm stuck on the design and hoped you guys could lend a hand. I'll try and be detailed so you have the info but sorry for the long post.
I'm designing an internal web site where users can go to request a domain ID for a new employee, a change to an existing ID, deactivation, etc. They can also specify what hardware, software and permissions they'll need for the user. The site pulls information from active directory to create a new record in the Users table whenever a user visits the site.
My database needs to store the user info for the one making the request and the one being requested as well as information about the request itself (when it was submitted, what type it is, etc.). It also needs to store info on the available hardware, software, permissions and licenses for the user to request.
So I have tables: Users, Requests, Hardware, Permissions, Software, Licenses, Statuses.
Users: Describes users.
Requests: Describes the requests. Has a foreign key to Users.
Hardware, Software, Permissions: Each of these describe a request for the given item - NOT the actual item on hand. Each has a foreign key to Requests.
Licenses: Describes the licenses we have on hand. Has a foreign key to Software.
Statuses: Describes the various statuses that can be assigned to each request. Each table above has a foreign key to Statuses.
I broke out Software, Hardware and Permissions tables because each request could include multiple "sub-requests" for each of these items. A new user might need a monitor, PC, MS Office, SAP, other software, access to 3 shared drives and more.
Ideally I'd like to have software, hardware, permissions and licenses tables that list what we actually have on hand and a request table that allows a user to request it. Then I could use the same database for inventory control, licensing, etc.
Should I add two columns to Requests, maybe "RequestsSubType" and "RequestsSubID" and each Type and ID would resolve to a specific table and a specific record in that table (respectively)? I'd have to hardcode the type to a certain table though... seems ugly.
Or should I add another "layer" of three tables to list specific items in each of the three categories (like Licenses already does) and have each request spread across fours "layers" of foreign keys? Seems ugly to me...
Or is there another solution I haven't thought of? (hope hope hope!)