0

I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table:

CREATE TABLE [PSDesk].[dbo].[Local](
    [UserID] [varchar](10) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [ComputerName] [varchar](15) NOT NULL,
    [IPAddress] [varchar](50) NOT NULL,
    [NETFramework] [varchar](50) NOT NULL,
    [ComputerModel] [varchar](50) NOT NULL,
    [UDrive] [varchar](50) NOT NULL,
    primary key (UserID, ComputerName)
)

I would like the UserID and ComputerName columns to be unique, meaning there will only be one record for each combination of UserID and ComputerName. Is this a good method to get the solution i'm looking for? I don't have much experience so i'm simply looking for advice, I threw this together just using my google-fu

6

Is that syntax correct? Yes. You could also create the PK afterward and with a few different syntax options, but this will build.

But there are a lot of answers to this question. And a lot of questions in this question. "Is this a good idea?" Well it depends – and that is sort of opinion and situation based so the question could be closed if not narrowed down a little with a more specific question.

When you define something as the primary key, you are stating that that is the fact or set of facts which describes one row and only one row.

So for what this table looks like the combination of UserID and ComputerName seem appropriate enough from a database design perspective alone.

There are still other questions though. Questions not in the question but things like:

  1. Why UserID as a varchar? Why not an INT that points back to a User(s) table? This would be a potentially more proper normal form.
  2. Same for ComputerName.
  3. What is the purpose of the table – seems like it describes a computer. Why have the user info in there at all? Why not have a User table and a Computer table and then a table which assigns a user to a computer? (Not saying one way is more right just a question.)
  4. The default behavior for SQL Server when creating a primary key is to make the index on the table clustered when doing it through the GUI – this isn't bad, but I like narrower clustered indexes and clustered indexes which are monotonously increasing and less likely to have index fragmentation and page splits. For that reason, I tend to be more often than not in the "composite clustered index" camp. Sometimes that means I make up a PK and let that be clustered like ComputerUserID – and have that be an identity column. Sometimes I just use that as the clustered and still use the natural key as the primary key as you are trying.
  5. If you created a surrogate key to be the PK for this table and be the clustered index, you could still create a unique index on the combination of ComputerName and UserID and enforce the uniqueness.

Just some food for thought. There are likely many answers to this question. Which is why I'm not 100% sure it will last, but here are some thoughts for now at least.

  • Thanks for your detailed response Mike, I definitely don't normally work with DBs so it's new to me. This DB is to store information about users and computers, I have built a UI that can search through the DB by either the UserID or ComputerName. The client that stores the information from each computer and UI console I mentioned are both built with PowerShell. I'll take your thoughts into consideration :) – cet51 Oct 17 '17 at 19:04
0

This is not 3rd normal form.

If two or more users share a computer then you would have duplicate entries for the computer. If there is no sharing then you can just use the computername alone.

Should have separate tables for user and computer. Then a 3rd junction table.

  • There can be multiple of the same UserID, as well as multiple of the same ComputerName. As long as there is only 1 record for each combination of UserID and ComputerName – cet51 Oct 17 '17 at 19:20
  • What part of that can require duplicate entries for computer and user data was not clear? – paparazzo Oct 17 '17 at 19:39
0

Would it be easier to split the table into two tables: one for user and the other for computer. Then you can establish your relational schema on both i.e. PK & FK! As far as I am aware of the Unique on primary key is to deter a one - to- one relation. In addition, why the user ID and Computer Name have the same data type of varchar?

  • That makes sense, at this point I just want a working version of everything i'm trying to accomplish. I'd say this is the like an 'alpha' stage of what i'm creating. When it comes to the overall design of the database and tables that is something I don't have knowledge of. Thankfully I have some other members of the IT department who have experience with Database administration when the times come to make everything more proper – cet51 Oct 17 '17 at 20:32
  • as for the datatypes, again just wanting everything to work :) might not be the best but i don't have that knowledge currently – cet51 Oct 17 '17 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.