Prev: Cross References Help
Next: Table design question
From: inungh on 12 May 2010 13:26
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,



From: Jeff Boyce on 12 May 2010 14:29
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" <inungh(a)gmail.com> wrote in message
news:ff4a0ea8-5858-4557-94da-b275266ed2a5(a)e35g2000yqm.googlegroups.com...
>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,
>
>
>


From: inungh on 12 May 2010 15:46
On May 12, 2:29 pm, "Jeff Boyce" <nonse...(a)nonsense.com> 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" <inu...(a)gmail.com> wrote in message
>
> news:ff4a0ea8-5858-4557-94da-b275266ed2a5(a)e35g2000yqm.googlegroups.com...
>
>
>
> >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,



 | 
Pages: 1
Prev: Cross References Help
Next: Table design question