Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

one to one relationship

Discussion in 'Information Technology' started by Vic, Jul 28, 2006.

Tags:
  1. Vic

    Vic
    Expand Collapse
    Guest

    Hi
    I am working on a db that has just one table with 75 fields.
    I'd like to split the table up. some of the fields can be put into separate
    tables and be joined by one-to-many relationships.
    But there is a lot of the fields that will only have one record.
    For example the Customer is in the main table with address, phone etc and in
    another table there will be the extra information for him like Registration,
    or Date joined which will only ever be once. Is that when you would use
    on-to-one?
    Can you please tell me how to do a one-to-one relationship. In Help there
    is a reference to it but there is no explanation or detail on how to.
    In the relationship window I drag Customer ID to Customer ID but the 'Create
    join' window just says 'one to many' at the bottom no matter what option I
    use.
    Thanks
    V
     
  2. Loading...

    Similar Threads Forum Date
    Relationship with Creator Blogs Oct 17, 2015
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Arts/Society What are your thoughts on the interaction of male / female relationship of marriage? Language, Arts & Culture Oct 15, 2013
    Relationship advice Love & Marriage Sep 30, 2012
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011

  3. Luiz Cláudio C. V. Rocha

    Luiz Cláudio C. V. Rocha
    Expand Collapse
    Guest

    Hi Vic,

    you should first create the primary key and the other indexes in your
    related tables, then create the relationships enforcing referential
    integrity. Access will set 1-to-1 for you.

    --
    Luiz Cláudio C. V. Rocha
    Coordenador de Projetos FórumAccess
    São Paulo - Brasil
    MVP Office
    http://www.msmvps.com/officedev


    "Vic" wrote:

    > Hi
    > I am working on a db that has just one table with 75 fields.
    > I'd like to split the table up. some of the fields can be put into separate
    > tables and be joined by one-to-many relationships.
    > But there is a lot of the fields that will only have one record.
    > For example the Customer is in the main table with address, phone etc and in
    > another table there will be the extra information for him like Registration,
    > or Date joined which will only ever be once. Is that when you would use
    > on-to-one?
    > Can you please tell me how to do a one-to-one relationship. In Help there
    > is a reference to it but there is no explanation or detail on how to.
    > In the relationship window I drag Customer ID to Customer ID but the 'Create
    > join' window just says 'one to many' at the bottom no matter what option I
    > use.
    > Thanks
    > V
    >
    >
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 16 Jul 2006 16:48:51 +0100, "Vic" <badspam@awyway.com> wrote:

    >Hi
    >I am working on a db that has just one table with 75 fields.
    >I'd like to split the table up. some of the fields can be put into separate
    >tables and be joined by one-to-many relationships.


    That's certainly important to do - and may be all that you need to do.

    >But there is a lot of the fields that will only have one record.
    >For example the Customer is in the main table with address, phone etc and in
    >another table there will be the extra information for him like Registration,
    >or Date joined which will only ever be once. Is that when you would use
    >on-to-one?


    If you're *certain* that these fields will never be repeated (could
    someone join, resign, and join a second time? if so would that be two
    registrations?), then I'd really recommend that you use a single
    table. There's no major overhead in having Null fields - Access
    doesn't waste disk space on them, regardless of the size of the field.

    There's a fair bit of overhead linking one to one tables, and they're
    needed only in certain circumstances. One common reason to use them is
    what's called "Subclassing": if an Entity has more than one distinct
    sets of KIND of attributes. For instance, an Insurance policy might
    have optional coverage for any combination of home insurance, auto
    insurance, business insurance, life insurance, etc.; it would make
    sense in this case to have a master policy table with the fields in
    common to all of them, related one-to-one to tables with the
    particular fields needed for the specific coverages.

    >Can you please tell me how to do a one-to-one relationship. In Help there
    >is a reference to it but there is no explanation or detail on how to.
    >In the relationship window I drag Customer ID to Customer ID but the 'Create
    >join' window just says 'one to many' at the bottom no matter what option I
    >use.


    If there is a unique index (such as a Primary Key) on the CustomerID
    field in both tables, the relationship will be one to one. If there is
    a unique index in only one of the tables, that table will be the "one"
    side. If there is no such index in either table, the relationship will
    be indefinite.

    In your case I would suggest not splitting the unique fields. Sure,
    normalize the fields that are validly "many" side fields; but if the
    customer's [Date Joined] is properly a static nonrepeating attribute
    of that customer, I see no benefit to pulling it out into another
    table.


    John W. Vinson[MVP]
     
  5. Vic

    Vic
    Expand Collapse
    Guest

    Thanks you for your advice. Firstly for explain the one-to-one steps, it
    made more sense now but mostly I'm so glad what you said coz I'm getting a
    real headache trying to figure out how to spit the rest of this table. I
    thought it wasn't good to have a table with so many fields, but for those
    that are static, they may as well stay put.
    V


    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:ucpkb25gebdb8gbluoqf89fs1u6qdakvh1@4ax.com...
    > On Sun, 16 Jul 2006 16:48:51 +0100, "Vic" <badspam@awyway.com> wrote:
    >
    >>Hi
    >>I am working on a db that has just one table with 75 fields.
    >>I'd like to split the table up. some of the fields can be put into
    >>separate
    >>tables and be joined by one-to-many relationships.

    >
    > That's certainly important to do - and may be all that you need to do.
    >
    >>But there is a lot of the fields that will only have one record.
    >>For example the Customer is in the main table with address, phone etc and
    >>in
    >>another table there will be the extra information for him like
    >>Registration,
    >>or Date joined which will only ever be once. Is that when you would use
    >>on-to-one?

    >
    > If you're *certain* that these fields will never be repeated (could
    > someone join, resign, and join a second time? if so would that be two
    > registrations?), then I'd really recommend that you use a single
    > table. There's no major overhead in having Null fields - Access
    > doesn't waste disk space on them, regardless of the size of the field.
    >
    > There's a fair bit of overhead linking one to one tables, and they're
    > needed only in certain circumstances. One common reason to use them is
    > what's called "Subclassing": if an Entity has more than one distinct
    > sets of KIND of attributes. For instance, an Insurance policy might
    > have optional coverage for any combination of home insurance, auto
    > insurance, business insurance, life insurance, etc.; it would make
    > sense in this case to have a master policy table with the fields in
    > common to all of them, related one-to-one to tables with the
    > particular fields needed for the specific coverages.
    >
    >>Can you please tell me how to do a one-to-one relationship. In Help there
    >>is a reference to it but there is no explanation or detail on how to.
    >>In the relationship window I drag Customer ID to Customer ID but the
    >>'Create
    >>join' window just says 'one to many' at the bottom no matter what option I
    >>use.

    >
    > If there is a unique index (such as a Primary Key) on the CustomerID
    > field in both tables, the relationship will be one to one. If there is
    > a unique index in only one of the tables, that table will be the "one"
    > side. If there is no such index in either table, the relationship will
    > be indefinite.
    >
    > In your case I would suggest not splitting the unique fields. Sure,
    > normalize the fields that are validly "many" side fields; but if the
    > customer's [Date Joined] is properly a static nonrepeating attribute
    > of that customer, I see no benefit to pulling it out into another
    > table.
    >
    >
    > John W. Vinson[MVP]
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    It's better to only select the fields you need for a form/query/report than
    actually split the table 1-to-1

    Pieter

    "Vic" <badspam@awyway.com> wrote in message
    news:ePRmHxPqGHA.612@TK2MSFTNGP03.phx.gbl...
    > Thanks you for your advice. Firstly for explain the one-to-one steps, it
    > made more sense now but mostly I'm so glad what you said coz I'm getting a
    > real headache trying to figure out how to spit the rest of this table. I
    > thought it wasn't good to have a table with so many fields, but for those
    > that are static, they may as well stay put.
    > V
    >
    >
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > news:ucpkb25gebdb8gbluoqf89fs1u6qdakvh1@4ax.com...
    >> On Sun, 16 Jul 2006 16:48:51 +0100, "Vic" <badspam@awyway.com> wrote:
    >>
    >>>Hi
    >>>I am working on a db that has just one table with 75 fields.
    >>>I'd like to split the table up. some of the fields can be put into
    >>>separate
    >>>tables and be joined by one-to-many relationships.

    >>
    >> That's certainly important to do - and may be all that you need to do.
    >>
    >>>But there is a lot of the fields that will only have one record.
    >>>For example the Customer is in the main table with address, phone etc and
    >>>in
    >>>another table there will be the extra information for him like
    >>>Registration,
    >>>or Date joined which will only ever be once. Is that when you would use
    >>>on-to-one?

    >>
    >> If you're *certain* that these fields will never be repeated (could
    >> someone join, resign, and join a second time? if so would that be two
    >> registrations?), then I'd really recommend that you use a single
    >> table. There's no major overhead in having Null fields - Access
    >> doesn't waste disk space on them, regardless of the size of the field.
    >>
    >> There's a fair bit of overhead linking one to one tables, and they're
    >> needed only in certain circumstances. One common reason to use them is
    >> what's called "Subclassing": if an Entity has more than one distinct
    >> sets of KIND of attributes. For instance, an Insurance policy might
    >> have optional coverage for any combination of home insurance, auto
    >> insurance, business insurance, life insurance, etc.; it would make
    >> sense in this case to have a master policy table with the fields in
    >> common to all of them, related one-to-one to tables with the
    >> particular fields needed for the specific coverages.
    >>
    >>>Can you please tell me how to do a one-to-one relationship. In Help
    >>>there
    >>>is a reference to it but there is no explanation or detail on how to.
    >>>In the relationship window I drag Customer ID to Customer ID but the
    >>>'Create
    >>>join' window just says 'one to many' at the bottom no matter what option
    >>>I
    >>>use.

    >>
    >> If there is a unique index (such as a Primary Key) on the CustomerID
    >> field in both tables, the relationship will be one to one. If there is
    >> a unique index in only one of the tables, that table will be the "one"
    >> side. If there is no such index in either table, the relationship will
    >> be indefinite.
    >>
    >> In your case I would suggest not splitting the unique fields. Sure,
    >> normalize the fields that are validly "many" side fields; but if the
    >> customer's [Date Joined] is properly a static nonrepeating attribute
    >> of that customer, I see no benefit to pulling it out into another
    >> table.
    >>
    >>
    >> John W. Vinson[MVP]

    >
    >
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    It's better to only select the fields you need for a form/query/report than
    actually split the table 1-to-1

    Pieter

    "Vic" <badspam@awyway.com> wrote in message
    news:ePRmHxPqGHA.612@TK2MSFTNGP03.phx.gbl...
    > Thanks you for your advice. Firstly for explain the one-to-one steps, it
    > made more sense now but mostly I'm so glad what you said coz I'm getting a
    > real headache trying to figure out how to spit the rest of this table. I
    > thought it wasn't good to have a table with so many fields, but for those
    > that are static, they may as well stay put.
    > V
    >
    >
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > news:ucpkb25gebdb8gbluoqf89fs1u6qdakvh1@4ax.com...
    >> On Sun, 16 Jul 2006 16:48:51 +0100, "Vic" <badspam@awyway.com> wrote:
    >>
    >>>Hi
    >>>I am working on a db that has just one table with 75 fields.
    >>>I'd like to split the table up. some of the fields can be put into
    >>>separate
    >>>tables and be joined by one-to-many relationships.

    >>
    >> That's certainly important to do - and may be all that you need to do.
    >>
    >>>But there is a lot of the fields that will only have one record.
    >>>For example the Customer is in the main table with address, phone etc and
    >>>in
    >>>another table there will be the extra information for him like
    >>>Registration,
    >>>or Date joined which will only ever be once. Is that when you would use
    >>>on-to-one?

    >>
    >> If you're *certain* that these fields will never be repeated (could
    >> someone join, resign, and join a second time? if so would that be two
    >> registrations?), then I'd really recommend that you use a single
    >> table. There's no major overhead in having Null fields - Access
    >> doesn't waste disk space on them, regardless of the size of the field.
    >>
    >> There's a fair bit of overhead linking one to one tables, and they're
    >> needed only in certain circumstances. One common reason to use them is
    >> what's called "Subclassing": if an Entity has more than one distinct
    >> sets of KIND of attributes. For instance, an Insurance policy might
    >> have optional coverage for any combination of home insurance, auto
    >> insurance, business insurance, life insurance, etc.; it would make
    >> sense in this case to have a master policy table with the fields in
    >> common to all of them, related one-to-one to tables with the
    >> particular fields needed for the specific coverages.
    >>
    >>>Can you please tell me how to do a one-to-one relationship. In Help
    >>>there
    >>>is a reference to it but there is no explanation or detail on how to.
    >>>In the relationship window I drag Customer ID to Customer ID but the
    >>>'Create
    >>>join' window just says 'one to many' at the bottom no matter what option
    >>>I
    >>>use.

    >>
    >> If there is a unique index (such as a Primary Key) on the CustomerID
    >> field in both tables, the relationship will be one to one. If there is
    >> a unique index in only one of the tables, that table will be the "one"
    >> side. If there is no such index in either table, the relationship will
    >> be indefinite.
    >>
    >> In your case I would suggest not splitting the unique fields. Sure,
    >> normalize the fields that are validly "many" side fields; but if the
    >> customer's [Date Joined] is properly a static nonrepeating attribute
    >> of that customer, I see no benefit to pulling it out into another
    >> table.
    >>
    >>
    >> John W. Vinson[MVP]

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 16 Jul 2006 18:21:28 +0100, "Vic" <badspam@awyway.com> wrote:

    > I
    >thought it wasn't good to have a table with so many fields


    It is unusually large - but if they are valid, atomic, nonrepeating
    attributes dependent *only* upon the Primary Key, then the design may
    be correct.

    If you wish to post the fieldnames and brief descriptions, someone
    might be able to give you a different viewpoint on whether they fit
    that description.

    John W. Vinson[MVP]
     

Share This Page