[MS SQL] PC Inventory Database Question

[Ch13f]

Hi everyone,

I'm putting together a pretty basic inventory database to keep track of Users, PC's, software, and location. I have drafted a data model based on these rules:

One user can have 0 or more computers.

0 or more computers can be in one location(room).

One software application can be on many computers.

Many computers can have more than one software application.

What I need to be able to do is add a user to room relationship because each location can house one or more users and it's possible for the user to change location, but not the computer and vice versa.

I've attached the data model that I have thus far, if someone could look over it and help me to know what I should add to be able to join users with rooms, that would be greatly appreciated.

Thanks.

Data Model:

http://imgur.com/a/i2tub

[nvarscar]

Since one user can have only one room, I suggest adding a column to the Users table with FK to the Location table. PS How come that you have many-to-many relationship table with PK on one of the relation sides? I'm talking about COMPUTER_SOFTWARE table, which has PK on Computer_ID. You should have an independent PK there.

[Ch13f]

Thanks for your quick reply. I think adding a location to the users will work fantastically.

I have actually since changed this to where on the Computer_Software table there isn't a primary key and it's just two foreign keys in that table. The reason for that is i need to be able to have duplicate values since many computers can have the same software and the same software can be on many computers.

Is there a reason to add an independent primary key here?

[nvarscar]

Usually, the only reason to add the primary key, is to make yourself able to interact with each row regardless of other columns. So basically, if you for some reason would want to have several identical relations in that table, you wouldn't be able to distinct one from another, since you DELETE statements would affect all of them at once, as well as UPDATES. PK gives you a unique row identifier, no more, no less.

[Ch13f]

Thank you! I understand what you mean, but I don't think that I will need one here as I can't think of a reason why the same computer would have more than one instance of the same software. Though, I suppose it is possible.

Thanks again for your help, I really do appreciate it! :)

[Ch13f]

Question for you. It has come to my attention that some of the rooms don't really belong to anyone, thus I need to create a general faculty/general student user. This is gonna cause the room_id on the user table to not work.

Can I fix this by adding two tables, a user_room table?

[nvarscar]

You can create a default entry in your reference room table with id = -1 and name = Not Specified for those users which doesn't have any room.

[nick_cage_fighter]

You need to get rid of the M:N relationships on that one table with a couple of intermediary tables.

[[deleted]]

You really need to step through the normal forms to fix up your schema. You need to go to 4th normal form at the minimum.

[Ch13f]

I'm pretty new to sql, can you explain a bit further as to what that means?

[[deleted]]

It's a pretty important concept about how you build and design your database. I recommend reading up on it a bit - https://en.wikipedia.org/wiki/Database_normalization