A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

move a primary key to a child



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 06:26 PM posted to microsoft.public.access.tablesdbdesign
inungh
external usenet poster
 
Posts: 177
Default move a primary key to a child

I want to add a child table to my table and using one of the primary
key of the table.
For example:

MyTable is

PKey1
PKey2
PKey3

I would like to move PKey3 out and add a child table as following and
remove PKey3 from MyTable:

ChildTable as folliwng
PKey1
PKey2
PKey3

MyTable will be

PKey1
PKey2

I just wonder what will impact in the future like reports, forms,
queries, data entries,
It seems that I need make a lot of changes for the application.
Any suggestions that should I continue to do this change?

Your information is great appreciated,



  #2  
Old May 12th, 2010, 07:29 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default move a primary key to a child

I'm not clear on what you mean by "move a primary key"...

In a well-normalized relational design, each table will have a primary key
field (or combination of fields). Those serve to uniquely identify the
records. If you removed one of a combination of fields that uniquely
identifies records, you would no longer have a way to uniquely identify
records!

According to your description, it appears that your Child table would have
the same three primary key fields. That does not compute! If they are
"child" records, they will have their own primary key field(s), and will
have a foreign key field (multiple fields if the primary key in the "parent"
is multiple fields).

Could you describe a bit more about what you're trying to do, and perhaps
offer example data?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"inungh" wrote in message
...
I want to add a child table to my table and using one of the primary
key of the table.
For example:

MyTable is

PKey1
PKey2
PKey3

I would like to move PKey3 out and add a child table as following and
remove PKey3 from MyTable:

ChildTable as folliwng
PKey1
PKey2
PKey3

MyTable will be

PKey1
PKey2

I just wonder what will impact in the future like reports, forms,
queries, data entries,
It seems that I need make a lot of changes for the application.
Any suggestions that should I continue to do this change?

Your information is great appreciated,





  #3  
Old May 12th, 2010, 08:46 PM posted to microsoft.public.access.tablesdbdesign
inungh
external usenet poster
 
Posts: 177
Default move a primary key to a child

On May 12, 2:29*pm, "Jeff Boyce" wrote:
I'm not clear on what you mean by "move a primary key"...

In a well-normalized relational design, each table will have a primary key
field (or combination of fields). *Those serve to uniquely identify the
records. *If you removed one of a combination of fields that uniquely
identifies records, you would no longer have a way to uniquely identify
records!

According to your description, it appears that your Child table would have
the same three primary key fields. *That does not compute! *If they are
"child" records, they will have their own primary key field(s), and will
have a foreign key field (multiple fields if the primary key in the "parent"
is multiple fields).

Could you describe a bit more about what you're trying to do, and perhaps
offer example data?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"inungh" wrote in message

...



I want to add a child table to my table and using one of the primary
key of the table.
For example:


MyTable is


PKey1
PKey2
PKey3


I would like to move PKey3 out and add a child table as following and
remove PKey3 from MyTable:


ChildTable as folliwng
PKey1
PKey2
PKey3


MyTable will be


PKey1
PKey2


I just wonder what will impact in the future like reports, forms,
queries, data entries,
It seems that I need make a lot of changes for the application.
Any suggestions that should I continue to do this change?


Your information is great appreciated,- Hide quoted text -


- Show quoted text -


Thanks for the message,

The application is to create meetings for the users.
Original design is to have one subject per meeting, so there is a
subjectID in my meeting table and Subject tabls as foreign table.
After years running, my client decided to allow multi subjects for the
one meeing.

I need add a table MeetingSubject as child of the meeting and need
remove SubjectID from Meeting table to add in MeetingSubject table.

It will be a lot of work to change data structure at this moment.

Please advise any other solution for this.

Thanks again for the information,



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.