Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a User table that contains the following fields

  • ID (Auto-generated GUID)
  • UserName
  • Password
  • FirstName
  • LastName
  • Email
  • AddressID (GUID)

This is linked to the table Address (1-1 relation) which contains the following fields

  • ID (auto-generated GUID)
  • Street
  • Other address fields

That seems to be the logical way of doing this (for me). But someone told me it may contain redundant field (AddressID) and that I should use EntityID instead of Address.ID.

There would be one less field, but the Address table would have its own ID field.

Which practice do you recommend? Is there one faster at execution? What's best in the context of parent/child relations? Is there an impact on performances?

share|improve this question

migrated from stackoverflow.com Nov 30 '11 at 18:37

This question came from our site for professional and enthusiast programmers.

3  
Your approach is fine - but I would try to avoid GUID's as primary (or more precisely: clustering keys) in SQL Server due to their awful performance (massive page splits, massive fragmentation) –  marc_s Nov 30 '11 at 15:55
3  
IF you want to enforce the 1-1 relationship then the ID of the user table should be the id of the address table. Otherwise an address can have multiple users or a user could have multiple addresses based on allowing having an Address_ID field. If you want to allow for scalability where sometime int he future a user COULD have more than one address or vice-versa, then you need the addressID and you don't have a 1-1 relationship. –  xQbert Nov 30 '11 at 15:59
    
@Marc: What do you suggest as primary key? I don't really like Auto increment integers as they are not easyly portable between databases. –  Mathieu Nov 30 '11 at 16:04
1  
@Mathieu: autoincrement INT might not be portable - but they are much better (much more efficient without any drawbacks) than GUID's from a database admin point-of-view. By default, I always use INT IDENTITY - unless I have a very strong and compelling reason not to.... –  marc_s Nov 30 '11 at 16:13
add comment

3 Answers

I agree with Maess - if you have distinct entities, each of those ought to have their own ID columns. But I have a strong objections against using GUID's for your primary keys - or more specifically - for your SQL Server's clustering keys.

GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

  • Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
  • 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

share|improve this answer
add comment

Right now you have a many to one relationship, where many users can have a one address ( there is no constraint on AddressID in User table).

for it to be truly one to one, where user is the principal and address is dependent, you need to create User.ID as the autogen primary key of user table and Address.Id of the non auto gen primary key and foreign key that references User.Id column.

EF would then recognize this as a valid one to one relationship.

share|improve this answer
add comment

If a table represents an entity and not a relation, it is best practice that it have it's own primary key. So, both User and Address should have their own PK and AddressID should be a FK in the User table. The approach you are following is correct.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.