Re: Single vs Multiple primary keys
On Sunday 15 May 2005 20:31, Dan Bolser wrote: > You must mean a multipart primary key with three parts :) > > or "multiple-column indexes" > > That is what I would do (use a multiple-column index (primary key) - its > kinda based on opinion, but I think you should let the real data be the > primary key where appropriate, and avoid artificial 'auto_increment' > unless they are specifically useful or necessary in your situation. > > I.e build the database around the data, not the other way round :) > > That is just my design preference though. > > Not sure about performance problems, but you get two 'indexes' for free > with one multipart primary key with three parts (so the order of the > parts is significant (depending on your application)). > I would advocate quite the opposite. The data is the data: primary/foreign keys are data about the database. You should always separate the two. For instance, the foreign key values used in a junction table, used to manage many-to-many relationships, are _simply_ foreign keys; the need for their presence in that particular table has more to do with normalisation and good database design than anything about the actual data in the real world. By all means, build the database AROUND the data, but don't actually USE the data to build the database. You can never really guarantee the uniqueness (or availability) of the data that you select for your primary key when you use _real_ data. A classic example, is where someone is using National Insurance numbers for employees an Employee database as the primary key for each employee - what happens if you suddenly start hiring foreign contractors, where no such data exists? Do you start inventing false data, just to satisfy your need for a foreign key? If you'd used auto-increment fields, the problem wouldn't arise. Furthermore, auto_increments are just integers: there is very little overhead involved in handling them. Real data is usually either more complex, or is apt to become so at some point in the future. Daniel Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
On Sun, 15 May 2005, Martijn Tonies wrote:
>Bob,
>
>> I have a table, see below, that contains a single primary key
>(SubTestCaseKey ) and a number of foreign keys
>>
>> * plantriggers_ID_FK ,
>> * testcase_root_ID_FK
>> * testcasesuffix_name_FK
>>
>> What I want to ensure is that there are no duplicate records when
>considering the three foreign keys above. Would it be appropriate to remove
>the single primary and replace with three multiple primary keys? Is there a
>performance impact when doing this. this seems overly complex and wonder if
>I should be breaking the table up to simplify? Any suggestions would be
>appreciated.
>>
>
>There's no such thing as 3 primary keys.
You must mean a multipart primary key with three parts :)
or "multiple-column indexes"
That is what I would do (use a multiple-column index (primary key) - its
kinda based on opinion, but I think you should let the real data be the
primary key where appropriate, and avoid artificial 'auto_increment'
unless they are specifically useful or necessary in your situation.
I.e build the database around the data, not the other way round :)
That is just my design preference though.
Not sure about performance problems, but you get two 'indexes' for free
with one multipart primary key with three parts (so the order of the
parts is significant (depending on your application)).
...
http://dev.mysql.com/doc/mysql/en/multiple-column-indexes.html
>
>Why do you need "subtestcasekey"? If the foreign key columns should be
>unique, why not
>make those 3 columns the primary key?
>
>With regards,
>
>Martijn Tonies
>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
>Server
>Upscene Productions
>http://www.upscene.com
>
>> Bob
>>
>> CREATE TABLE testplans (
>> SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
>> plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
>> testcase_root_ID_FK INTEGER NOT NULL,
>> testcasesuffix_name_FK VARCHAR(20) NULL,
>> FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
>> Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
>> DateMod TIMESTAMP NULL,
>> tester_list_Name_FK VARCHAR(50) NULL,
>> PRIMARY KEY(SubTestCaseKey),
>> INDEX testplans_FKIndex1(tester_list_Name_FK),
>> INDEX testplans_FKIndex2(testcasesuffix_name_FK),
>> INDEX testplans_FKIndex3(testcase_root_ID_FK),
>> INDEX testplans_FKIndex4(plantriggers_ID_FK),
>> FOREIGN KEY(tester_list_Name_FK)
>> REFERENCES tester_list(Name)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(testcasesuffix_name_FK)
>> REFERENCES testcasesuffix(name)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(testcase_root_ID_FK)
>> REFERENCES testcase_root(ID)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(plantriggers_ID_FK)
>> REFERENCES plantriggers(ID)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE
>> )
>> TYPE=InnoDB;
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
Bob,
> I have a table, see below, that contains a single primary key
(SubTestCaseKey ) and a number of foreign keys
>
> * plantriggers_ID_FK ,
> * testcase_root_ID_FK
> * testcasesuffix_name_FK
>
> What I want to ensure is that there are no duplicate records when
considering the three foreign keys above. Would it be appropriate to remove
the single primary and replace with three multiple primary keys? Is there a
performance impact when doing this. this seems overly complex and wonder if
I should be breaking the table up to simplify? Any suggestions would be
appreciated.
>
There's no such thing as 3 primary keys.
Why do you need "subtestcasekey"? If the foreign key columns should be
unique, why not
make those 3 columns the primary key?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
> Bob
>
> CREATE TABLE testplans (
> SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
> plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
> testcase_root_ID_FK INTEGER NOT NULL,
> testcasesuffix_name_FK VARCHAR(20) NULL,
> FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
> Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
> DateMod TIMESTAMP NULL,
> tester_list_Name_FK VARCHAR(50) NULL,
> PRIMARY KEY(SubTestCaseKey),
> INDEX testplans_FKIndex1(tester_list_Name_FK),
> INDEX testplans_FKIndex2(testcasesuffix_name_FK),
> INDEX testplans_FKIndex3(testcase_root_ID_FK),
> INDEX testplans_FKIndex4(plantriggers_ID_FK),
> FOREIGN KEY(tester_list_Name_FK)
> REFERENCES tester_list(Name)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(testcasesuffix_name_FK)
> REFERENCES testcasesuffix(name)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(testcase_root_ID_FK)
> REFERENCES testcase_root(ID)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(plantriggers_ID_FK)
> REFERENCES plantriggers(ID)
> ON DELETE RESTRICT
> ON UPDATE CASCADE
> )
> TYPE=InnoDB;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single vs Multiple primary keys
I have a table, see below, that contains a single primary key (SubTestCaseKey )
and a number of foreign keys
* plantriggers_ID_FK ,
* testcase_root_ID_FK
* testcasesuffix_name_FK
What I want to ensure is that there are no duplicate records when considering
the three foreign keys above. Would it be appropriate to remove the single
primary and replace with three multiple primary keys? Is there a performance
impact when doing this. this seems overly complex and wonder if I should be
breaking the table up to simplify? Any suggestions would be appreciated.
Bob
CREATE TABLE testplans (
SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
testcase_root_ID_FK INTEGER NOT NULL,
testcasesuffix_name_FK VARCHAR(20) NULL,
FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
DateMod TIMESTAMP NULL,
tester_list_Name_FK VARCHAR(50) NULL,
PRIMARY KEY(SubTestCaseKey),
INDEX testplans_FKIndex1(tester_list_Name_FK),
INDEX testplans_FKIndex2(testcasesuffix_name_FK),
INDEX testplans_FKIndex3(testcase_root_ID_FK),
INDEX testplans_FKIndex4(plantriggers_ID_FK),
FOREIGN KEY(tester_list_Name_FK)
REFERENCES tester_list(Name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcasesuffix_name_FK)
REFERENCES testcasesuffix(name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcase_root_ID_FK)
REFERENCES testcase_root(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(plantriggers_ID_FK)
REFERENCES plantriggers(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: <[EMAIL PROTECTED]>
Pgr: <[EMAIL PROTECTED]>
