473,883 Members | 2,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

composite primary key

i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?
Jul 20 '05 #1
5 14954
Hi

I think this is probably a candidate for a surrogate key.

John
"onder" <ok****@hotmail .com> wrote in message
news:9c******** *************** ***@posting.goo gle.com...
i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?

Jul 20 '05 #2
Hi John,

sorry for this but can you tell a little more on that?
Jul 20 '05 #3
Hi

There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.

Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/de...asp?frame=true
but it is possible to maintain the values your self.

HTH

John
"onder" <ok****@hotmail .com> wrote in message
news:9c******** *************** ***@posting.goo gle.com...
Hi John,

sorry for this but can you tell a little more on that?

Jul 20 '05 #4
Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?
Jul 20 '05 #5
Hi

You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,

It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.

John
"onder" <ok****@hotmail .com> wrote in message
news:9c******** *************** ***@posting.goo gle.com...
Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2650
by: Girish Agarwal | last post by:
--0-474210375-1058976151=:31789 Content-Type: text/plain; charset=us-ascii Content-Id: Content-Disposition: inline Note: forwarded message attached. __________________________________
5
6934
by: John | last post by:
Specifically for joint tables... tblStudents tblClasses tblClasses_Students Is it be good programming to use a composite primary key in tblClasses_Students (where the key is ClassID and StudentID)? I enventually will convert the Access db to an SQL db. I've always used single primary keys based on long integers, usually autonumbered.
10
16532
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and selecting Primary Key. But I've heard that's not a good idea. What I want to do is use table constraints to ensure only unique transactions - defined by a combination of amount, date, account and customer - are entered in the table while leaving the AutoNumber Transaction_ID the sole field in...
18
12677
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many relations with the table containing no primary key. Do I just create two primary keys on the table that does not contain any primary key for this to become a composite primary key? Thank you in advance! Still a newbie,
4
4061
by: Ismail Rajput | last post by:
Is there any option we can use Composite DataKeyField in the DataList and DataGrid?
2
2242
by: Peter Lindquist | last post by:
I had a fun issue this morning, and now I'm wondering if I violated a rule I didn't know about. Any insight would be much appreciated. All operations involving this particular table may be assumed to have functioned normally before today. bash-2.05a$ cat PG_VERSION 7.3 Last night, we added 'index_t_payment_param' to this table. payment_id is not a unique entry, hence the composite primary key, and I've since
7
5619
by: Ronald S. Cook | last post by:
My client manager likes concatenated/composite primary keys. I don't. Can anyone forward any arguments pro or con? Thanks, Ron
4
2977
by: Wolfgang Keller | last post by:
Hello, so far it seems to me as if the only ORM module for Python which supports composite primary/foreign keys was SQLAlchemy. Which looks a little bit "overbloated" for my needs: I "just" need to be able to define a "logical model" (à la UML) in Python and have the ORM connect to a database (running on PostgreSQL in my case) which uses a corresponding (pre-defined) "physical model" as its schema. Modeling really does look exactly...
2
3516
by: lfhenry | last post by:
Hi All, I am about to make some changes to a process we have. The new process will have a new table who's job it is to store temporarily data such as customer, clerkno,productno, storeno , amount, time. On a new request from a front-end i will select against the primary keys to ensure no other request for the same data has been made. This is then sent to a backend system asynchronously if there is no match. I will otherwise send a...
54
4051
by: csolomon | last post by:
Hello: I was wondering if I could get some input on how to address a design issue, involving my composite table. I have one portion of my project complete. The following forms and reports I will add, piggyback off of my existing design. The part I have already completed allows my users to create a design sample; this is made up of several materials to create one sample. I have accomplished this using this design:
0
9797
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10762
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9586
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7977
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6005
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.