Stairway to Database Design - STEP 1: Data Elements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joe Celko » Stairway to Database Design - STEP 1: Data...

43 posts, Page 5 of 5

«««1 2 3 4 5

Stairway to Database Design - STEP 1: Data Elements

Rate Topic

Display Mode

Topic Options

Author

Message

Jason Whitney

Posted Tuesday, September 04, 2012 9:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:50 AM
Points: 35, Visits: 507

Thank you for your reply. I understand that I should use a natural key when one exists, however, for my customers, there is no natural key.
e-mail address: This is bricks and mortar so this information is not collected. Even if it was, it doesn't uniquely identify a customer as some people share addresses.
DUNS: The customers are consumers not businesses so this isn't applicable.
Credit Card: This identifies the payer, not the customer. Additionally, people will have multiple credit cards, and cards will be shared between multiple people.

I could continue down the list of options, but the conclusion is that there is nothing our customers can provide that we can guaranty to be unique. We have to provide a unique identifier to our customers. I can write a program to generate random (or sequential) numbers for the customer ID, or I can use an identity column (or something else)?

In this case, why wouldn't I use an identity column? I want to make sure I get my design right, and I am having trouble with tables that have no natural key. What do I use?

Post #1353991

John Mitchell-245523

Posted Wednesday, September 05, 2012 1:44 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 4,418, Visits: 7,173

If you have to provide that unique identifier to your customers, that's a good reason not to use an identity property to generate it. It makes it too easy to guess one customer's ID given another's.

John

Post #1354362

davidandrews13

Posted Monday, April 15, 2013 4:50 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 455, Visits: 2,666

this is an interesting article, and one that i have only just got round to reading.
i have a few questions which i would like to ask, just to expand my understanding.
in the first page of the comments, Celko mentions

The old IDEF standard used singular names for files because you process the records of the file one at a time. In the RDBMS world, we process an entire set of rows at a time, so we use collective names (or plural if no collective is available). If you do have only one Customer, then that would be the correct table name. Sorry business is so bad :)

my query is this: what if you start off with only one Customer, e.g You have exclusivity to one Customer for 12 months. After that, you may get more Customers but there's no real guarantee. depends on how good you are i suppose!
would you name the table Customer or Customers? changing the table name at a later date wouldn't be advisable.

Daniel Bowlin brings up the issue of the article stating you shouldn't use the format <table name>_id for you column name.

DEK46656 states that he believes its about calliong the table 'Customers' and the column 'Customer_id'.
i'm wondering if there is a 'Standards' reply; perhaps Celko knows? i couldn't see a specific response from him about this.

Post #1442249

« Prev Topic | Next Topic »

43 posts, Page 5 of 5

«««1 2 3 4 5

Permissions