DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

Hi,

UserID is the promary key in Users table (like aspnet_Users) and a FK in related tables (like UserProfile etc). Since each profile can be identified by the userID only, there is nothing wrong in having it as the PK of the same table. Why would you like to have a surrogate key for such a table (when it doesnt really need one). IMHO, this is fine.

Vivek

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

This approach -- using the same name in the related table as in the

primary -- is appropriate when the related table is a subset of the

objects in the primary table, rather than a linking table between two or more primary entities, and has a

one-to-one relationship to the primary table. Usually the related

table is created to avoid nulls in a core table.

A product

inventory might be an example. It makes sense to have a unique

product_id for each product manufactured by a company. This would of

course be the primary key for the master "products" table. You would

not want to have information specific to a particular line of producs

in this table, though. Say the comapny makes tools, and one of their

lines is sockets. Fields such as socket_type [e.g. hex, star],

drive_size, or socket_size would be irrelevant to many other tools, but

quite necessary to the sockets line. Therefore, you might have a data

structure like:

tools (
product_id char(12) primary key,
tool_class varchar(64),
price decimal,
...
);

sockets (
product_id char(12) primary key references tools(product_id),
socket_type smallint,
drive_size decimal,
socket_size decimal,
...
);

This

works because all sockets are tools, though many tools are not

sockets. Likewise, I would choose product_id here, rather than say

tool_id, for flexibility. The company may also carry other lines of

products for which tool_class would be an inappropriate field. They

are still all products, though.

Hope this helps,

Joseph

db performance on san mapped to one drive

I'm working on a performance problem on a SQL server 2000 W2K SP4
Active / Active cluster. The primary database (bigdb) is 140 Gb and
has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
performance tuning. The system is all up to par with patches etc.
The disk is a screaming fast SAN with great cache. The databases are
all on a single drive mapped to H: for this instance. Here's some
results from a ~20 hr perfmon analysis with ~4500 values of drive H:
CounterAverageMinMax
Disk Bytes / sec: 4,406,00052,00039,912,000
Disk Queue Length:3.500255
%Disk Time:3440.1215,500
So, I'm wondering if separating the databases onto different drive
letters would help disk time and queue length. For example: tempdb on
H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
etc. on J:. All of these would still on the same san fiber, but maybe
W2K would do a better job managing the data using multiple disks to
the same SAN? Thoughts?
Are there any tuning tweeks I can make to W2K to give more priority to
the SQL SAN drive? The internal drives are not have any issues...
Hi
The disk queues do not look too good.
What type of SAN is it? EMC? Depending on the SAN technology, the disk
layout can play a big role. RAID-10 preferably.
It does not help splitting up the volume into different volumes / drive
letters, unless the volumes are going to be on different spindles.
The Transaction log writes are generally sequential, whilst the database is
usually random. Having the same disks try to service both types of requests
leads to terrible disk queue lengths.
The OS can't do much more to help the SAN drives, they are the ones not
coping.
With 4GB or RAM and 32 Bit OS, SQL Server can only use up to 3GB and the OS
gets the other 1GB (assuming /3GB is in boot.ini) Considering the load on
the server, 4GB is a bit little. 8GB would start to sound right, 16 might
become very sweet. With more RAM, SQL Server can cache more, resulting in
less reads being required. In effect, you have too many processors for the
RAM.
There are other factors that can bury the best hardware. Bad database and
index design, and terrible queries. e.g bad indexing results in SQL Server
having to read though all the data in a table, this causes more read
requests. this slows down writes, which in turn slow down reads...on and on
and on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<paulaner@.iname.com> wrote in message
news:0hdka111vmelvphc86a7rrpcopjpdjt8d9@.4ax.com...
> I'm working on a performance problem on a SQL server 2000 W2K SP4
> Active / Active cluster. The primary database (bigdb) is 140 Gb and
> has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
> The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
> only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
> performance tuning. The system is all up to par with patches etc.
> The disk is a screaming fast SAN with great cache. The databases are
> all on a single drive mapped to H: for this instance. Here's some
> results from a ~20 hr perfmon analysis with ~4500 values of drive H:
> Counter Average Min Max
> Disk Bytes / sec: 4,406,000 52,000 39,912,000
> Disk Queue Length: 3.50 0 255
> %Disk Time: 344 0.12 15,500
> So, I'm wondering if separating the databases onto different drive
> letters would help disk time and queue length. For example: tempdb on
> H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
> etc. on J:. All of these would still on the same san fiber, but maybe
> W2K would do a better job managing the data using multiple disks to
> the same SAN? Thoughts?
> Are there any tuning tweeks I can make to W2K to give more priority to
> the SQL SAN drive? The internal drives are not have any issues...
>
>