| Author |
Topic |
|
nbourre
Starting Member
14 Posts |
 Posted - 2008-09-10 : 17:33:31
|
| Hi,Is there a way where I could create a primary key which is composed of a concatenation from an identity field and the computer mac address?pkRowID = identityField + computerMACAddressThank youNickBeginning with something is a good start! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 17:36:51
|
Why?The identity column already makes the row unique.You can make a composite primary key however.CREATE UNIQUE NONCLUSTERED INDEX IX_Yak ON Yak (IdentityCol, macAddr)"CREATE PRIMARY KEY IX_Yak ON Yak (IdentityCol, macAddr)"
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-10 : 18:03:44
|
quote:
Originally posted by nbourre
Hi,Is there a way where I could create a primary key which is composed of a concatenation from an identity field and the computer mac address?pkRowID = identityField + computerMACAddressThank youNickBeginning with something is a good start!
Why do you want the mac address ? Do you want to track the computer or what ?That can be done in c# or you can track users (if thats the case) with cookies |
|
|
|
nbourre
Starting Member
14 Posts |
Posted - 2008-09-10 : 21:41:14
|
| This is because, some laptops will need to be offline when the employees work on the field. And I'll need to resync the data with the server.NickBeginning with something is a good start! |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-10 : 23:25:46
|
| Does each user have a completely separate set of data? If they update shared data you'll have to consider how to manage updates to the same row/record by multiple users. This is a big issue to handle/code manually. |
|
|
|
nbourre
Starting Member
14 Posts |
Posted - 2008-09-11 : 02:09:13
|
| Actually, each record will be on the main server. A single record can only be access by one user at a time in read-write mode, but by "unlimited" users in read-only. When a user has to go out with a laptop, he will need to bring a copy of the needed records outside the main DB. This action will block the selected records from any modification. After the modification on the field, he will sync the data with the database. The employee would also be able to create a record offline and insert it inside the main DB. This is the reason for a unique identifier which is not an identity, because many computer could be outside.I guess there will be a sync state field. Something like 0 = RW available, 1 = RO, 2 = OutsideSo a primary create with an identity concatenated with a MAC address would give a unique key. Maybe a GUID could also do the job.What do you think of that solution?NickBeginning with something is a good start! |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-11 : 03:16:08
|
| Consider this scenario-User U1 selects rows 1-1000 to copy for field & they are blocked for update (Sync state 1 or 2)User U1 searches for data like "Name 1" & finds none so he creates a new record called "Name 1"User U2 takes rows 1-10 in his set to take off-siteUser U2 searches for data like "Name 1" & finds none so he creates a new record called "Name 1"User U3 takes rows 10-20 in his set to take off-siteUser U3 searches for data like "Name 1" & finds none so he creates a new record called "Name 1"--User U2 wants to sync his records & they are sync state 1 or 2 - ie not updatable. Can he change them to updateable & run his updates? Options with this solution: wait or run updates--> Insert goes thru OK as there is a unique keyUser U3 wants to sync his records & they are sync state 1 or 2 - ie not updatable. Can he change them to updateable & run his updates? Options with this solution: wait or run updates--> Insert goes thru OK as there is a unique keyUser U1 wants to sync his records & they are sync state 1 or 2 - ie not updatable. Can he change them to updateable & run his updates? Options with this solution: wait or run updates--> Insert goes thru OK as there is a unique key--------------------------With the inserts, you end up with 3 distinct keys & data called "Name 1".------------------If you disallow updates, users have to wait for others to update their rows before they can. Even with this there is still the possibility of the same data being changed by different people, then you need rules for how to manage this. If you allow updates, you still have this issue.I was responible for a distributed system like this on a couple of different occasions & you can't rely on users syn-ing regularly- even when it's in their KPIs they still find excuses, go on holidays & get sick etc. You also need to manage partial syncs in case of line/connection drop-out. The technical solution for a distributed system like this is really complicated.My recommendation is use something out-of-the-box. |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-11 : 03:22:53
|
| SQL Server supports remote user synchonisation |
|
|
|
|
Create a primary key with MAC address and identity