Best Primary Key Solution?

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)

A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.

"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.

-PatP

Best practise of storing a column which is of string type

Hi,

Hi I am currently using SQL Server 2005.

Whats the difference between nvarchar & varchar datatypes in SQL server and which is opted best to store strings.

Thanks,

Uma Ramiya

nvarchar is used commonly used to store national characters / unicode characters. so if your front end application accepts unicode characters to be stored to your db then you should use nvarchar. if not use varchar instead
|||

I use ASp.Net 2.0 as front end. In that case what should I use

Thanks,

Uma Ramiya

|||its not dependent on asp.net. it depends on the localization of your application e.g. does your asp.net application can be viewed, transalated and/or accepts inputs with chinese , korean characters, etc. then you should use nvarchar. if you application only stores english characters then use varchar
|||

If you want to store english (latin based) string then you can use varchar.

If you want to store non-english string (ex. Japanese, Hindi, Tamil etc) then you have to use NVarchar.

Nvarchar uses Unicode (which supports all the alphabets/numbers)

Varchar Uses ASCII CODE (which supports only english alphabets/numbers)

Nvarchar each char occupies 2 Byte

Varchar each char occupies 1 Byte

Nvarchar max length in SQL Server 2000 = 4000

Varchar max length in SQL Server 2005 = 8000

If your app supports (or planned to support on future) Globilization/Localization then use the NVarchar

Otherwise Varchar is more enough..

Best practices: changing values

What is the best way to design your tables in cases where field values change?

Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);

CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);

Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)

is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:

CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);

and have the Purchase table reference the ProductPrice table instead of the products table?

I have used both methods in the past, but I was wanted to get other peoples' take on it.

ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.

-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)

No, it doesn't. :) It's an attribute of the purchase.

The purchase table should have the price paid at time of purchase.

There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.

Best Data type to hole phonenumber

Whats the best type to hold a phone number in this format with a column in my sql table...should i use varchar? or something else....any help would be great!!

Is this phone number US-only or is it international?

Thanks

Waseem

|||its only US|||

Take a look at this example

http://www.rampant-books.com/t_super_sql_128_user-defined_data_types.htm

Hope this helps

Best Advice

I have a table containing 65 fields. One of the fields is a varchar(6) field
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am sing in this case ane you '111111' the
client number
Thanks
"Aaron [SQL Server MVP]" wrote:

> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am sing in this case ane you '111111' the
> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a