0

I have read here that it is probably better to use an AI integer to denote users as opposed to an assigned domain (string) username. I can accept that, however, when I am including in the 'users' table the auto generated userid and the unique domain usernames, how should I denote that in other tables as FKs? eg. Department table with 'userid' AND 'domainname' or just userid; hardware table with 'userid' AND 'domainname' or just 'userid' as owner?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
ksdst1
  • 35
  • 1
  • 7
  • it's not clear exactly what you're asking... can you give us more info about your table structure and what you're trying to achieve with it? – Taryn East Nov 11 '14 at 03:54
  • Its to track users, their hardware, network port/IP/MAC address, location, software. etc. I want to use their domain userids as their unique identifier as they can't be a user without one. Is it more proper to use an auto generated ID number for rows in tables? Is it over-kill to use this AND their unique domain user account id (varchar not INT)? I'd prefer to use the domain user account id eg. sdr35. As an analogy, when a DB with a user table can use a social security number as a unique primary identfier/key, is it convention to instead use an auto INT id to be the PK instead? ...tnx – ksdst1 Nov 11 '14 at 16:41

2 Answers2

0

I'll give you some Idea but not exact answer. -"this answer is base on my understanding in your question"

Design example :

Table Users:

Your primary key here is UserId and you can use this as foreign key in other table.

UserId | Usernames

Table Department:

Your primary key here is DepartmentId and you can use this as foreign key in other table.

You also have a foreign key called UserId from table Users

DepartmentId | UserId |DomainName

Table Hardware:

Your primary key here is HardwareId

You also have 2 foreign keys called DepartmentId and UserId

HardwareId | DepartmentId | UserId (optional) | Etc...

OK, Each table must and required to have a own primary key and you can't use the DepartmentId and UserId to become primary key in table Hardware because they are foreign key in this table.

DepartmentId - Include, so you will know the department where the hardware base.

UserId - (Optional), You will know the owner or user of this hardware by using only the UserId

Onel Sarmiento
  • 1,608
  • 3
  • 20
  • 46
  • Its to track users, their hardware, network port/IP/MAC address, location, software. etc. I want to use their domain userids as their unique identifier as they can't be a user without one. Is it more proper to use an auto generated ID number for rows in tables? Is it over-kill to use this AND their unique domain user account id (varchar not INT)? I'd prefer to use the domain user account id eg. sdr35. As an analogy, when a DB with a user table can use a social security number as a unique primary identfier/key, is it convention to instead use an auto INT id to be the PK instead? ...tnx – ksdst1 Nov 11 '14 at 16:46
  • Of course you can use the `unique domain id` and `SSS` as pk but be sure those value will not be repeated in primary key. see [varchar PK](http://stackoverflow.com/questions/19299874/can-i-use-varchar-as-the-primary-key) and [best practice PK](http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables) – Onel Sarmiento Nov 11 '14 at 23:24
0

Just the user-ID. This is a key component of database normalization; if the other tables contain any other information that can be completely determined by the user, then you introduce the risk of inconsistencies whereby different records make different claims about a given user.

ruakh
  • 175,680
  • 26
  • 273
  • 307