store info in database.

Discussion in 'ASP General' started by Christopher Brandsdal, Sep 4, 2003.

  1. Hi!

    I have a delicatg problem....
    I have made a registration form for adding my friends information in a
    database. The problem is that I want to connect the persons with companies
    in the same database. I wonder how i could connect a person to several other
    id's in a table.

    I wonder if I can make a string in the database that holds all the id's
    separated with , for example....?

    the string would then look like this:
    1,4,6,22,56
    for example...

    But how can I then open this string and separate the numbers from the , ???

    Hmm..
    This may be difficult to ynderstand, but I have tried my best to explain ;)

    Christopher Brandsdal
     
    Christopher Brandsdal, Sep 4, 2003
    #1
    1. Advertising

  2. Christopher Brandsdal

    TomB Guest

    If you have two tables in your database, one called Companies and one called
    People, you have to make a decision....

    If you just want to track a person's current company, and you are assuming
    that the people only work for one company at a time, then you add a
    CompanyID foreign key field to the People table. This is a one-to-many
    relationship with the company being on the "one" side and the People being
    the "many", thus one company has many people working for it.

    If you want to track a person's employment history, or allow for multiple
    companies then you need to create a many-to-many relationship-- meaning one
    company has many people working for it, but also one person has worked (or
    is working) for many companies.

    To create the many-to-many relationship you create a "join" table, which
    contains foreign keys to both the Company table and the People table.

    Usually, the join table's primary key is the combination of the Foreign
    Keys. This prevents duplicate entries. There are times, however; when a
    duplicate entry would be acceptable. For example: if you are tracking an
    employment history, it could be possible for a person to work at one
    company, leave it, then come back to it. If you'd used the Foreign keys as
    Primary key, then you wouldn't be able to add the return to the same
    company.

    Your idea to store the information in a single field (comma separated) is a
    problematic solution. Besides being difficult to maintain and parse....how
    would you decide how much data the field could hold?




    "Christopher Brandsdal" <> wrote in message
    news:...
    > Hi!
    >
    > I have a delicatg problem....
    > I have made a registration form for adding my friends information in a
    > database. The problem is that I want to connect the persons with companies
    > in the same database. I wonder how i could connect a person to several

    other
    > id's in a table.
    >
    > I wonder if I can make a string in the database that holds all the id's
    > separated with , for example....?
    >
    > the string would then look like this:
    > 1,4,6,22,56
    > for example...
    >
    > But how can I then open this string and separate the numbers from the ,

    ???
    >
    > Hmm..
    > This may be difficult to ynderstand, but I have tried my best to explain

    ;)
    >
    > Christopher Brandsdal
    >
    >
    >
     
    TomB, Sep 4, 2003
    #2
    1. Advertising

  3. Hello,

    Use the split command, which will then parse out all the commas and put all
    the numbers into an array.


    Dim tempStr
    tempStr = "1,2,3,4,5,6,7,8"

    Dim tempArray
    tempArray = split(tempStr, ",")

    Dim maxArrayElements
    maxArrayElements = UBound(tempArray)

    Dim arrayIndex
    arrayIndex = 0

    For arrayIndex = 0 to maxArrayElements - 1
    ' do what you need to do here, using
    ' tempArray(arrayIndex)
    ' to get at the current array value
    Next

    This example is in vbscript, although Javascript also supports the SPLIT
    command....

    Joe



    "Christopher Brandsdal" <> wrote in message
    news:...
    > Hi!
    >
    > I have a delicatg problem....
    > I have made a registration form for adding my friends information in a
    > database. The problem is that I want to connect the persons with companies
    > in the same database. I wonder how i could connect a person to several

    other
    > id's in a table.
    >
    > I wonder if I can make a string in the database that holds all the id's
    > separated with , for example....?
    >
    > the string would then look like this:
    > 1,4,6,22,56
    > for example...
    >
    > But how can I then open this string and separate the numbers from the ,

    ???
    >
    > Hmm..
    > This may be difficult to ynderstand, but I have tried my best to explain

    ;)
    >
    > Christopher Brandsdal
    >
    >
    >
     
    Joseph Narissi, Sep 4, 2003
    #3
  4. Ok. Thanks I understand.

    But the the problem is that both companies and persons are in the same
    table...

    Should I change it to 2 different tables???

    and how will the code look like with the join command???

    When I add a person to the database I have to be able to select multiple
    companies for that person...
    (the companies are added in advance)



    "TomB" <> skrev i melding
    news:...
    > If you have two tables in your database, one called Companies and one

    called
    > People, you have to make a decision....
    >
    > If you just want to track a person's current company, and you are assuming
    > that the people only work for one company at a time, then you add a
    > CompanyID foreign key field to the People table. This is a one-to-many
    > relationship with the company being on the "one" side and the People being
    > the "many", thus one company has many people working for it.
    >
    > If you want to track a person's employment history, or allow for multiple
    > companies then you need to create a many-to-many relationship-- meaning

    one
    > company has many people working for it, but also one person has worked (or
    > is working) for many companies.
    >
    > To create the many-to-many relationship you create a "join" table, which
    > contains foreign keys to both the Company table and the People table.
    >
    > Usually, the join table's primary key is the combination of the Foreign
    > Keys. This prevents duplicate entries. There are times, however; when a
    > duplicate entry would be acceptable. For example: if you are tracking an
    > employment history, it could be possible for a person to work at one
    > company, leave it, then come back to it. If you'd used the Foreign keys

    as
    > Primary key, then you wouldn't be able to add the return to the same
    > company.
    >
    > Your idea to store the information in a single field (comma separated) is

    a
    > problematic solution. Besides being difficult to maintain and

    parse....how
    > would you decide how much data the field could hold?
    >
    >
    >
    >
    > "Christopher Brandsdal" <> wrote in message
    > news:...
    > > Hi!
    > >
    > > I have a delicatg problem....
    > > I have made a registration form for adding my friends information in a
    > > database. The problem is that I want to connect the persons with

    companies
    > > in the same database. I wonder how i could connect a person to several

    > other
    > > id's in a table.
    > >
    > > I wonder if I can make a string in the database that holds all the id's
    > > separated with , for example....?
    > >
    > > the string would then look like this:
    > > 1,4,6,22,56
    > > for example...
    > >
    > > But how can I then open this string and separate the numbers from the ,

    > ???
    > >
    > > Hmm..
    > > This may be difficult to ynderstand, but I have tried my best to explain

    > ;)
    > >
    > > Christopher Brandsdal
    > >
    > >
    > >

    >
    >
     
    Christopher Brandsdal, Sep 4, 2003
    #4
  5. Christopher Brandsdal

    TomB Guest

    Unless there's a REALLY good reason, then you should separate the data into
    two tables. Try to think of the contents of a table as being a list of
    similar entities. So Companies would be in a separate table from People,
    because they are dissimilar.

    If you are using Access create the tables you need, then go the
    relationships (icon on toolbar) to indicate how the tables are related.
    Once Access "knows" how the tables are related, you can go into the Query
    Wizard and create a query (or use design view)
    View the query to ensure the results are what you expect.
    Switch to SQL view of the query, and you'll see the SQL statement needed to
    select the data.

    When you are adding people to database, you want to add multiple companies.
    I assume therefore, that you'll have three tables (many to many)

    Present the form with fields for the Person information, and checkboxes for
    the available companies. To make life easier, give each checkbox the same
    name, but a different value. For example:

    <input type=checkbox name=Companies value="3">McDonalds
    <input type=checkbox name=Companies value="4">Burger King

    Where 3 and 4 are the UniqueIDs of the two companies.

    On the page the form is posted to, "Requesting" the Companies will return a
    comma separated list of the selected companies. So if both of the values
    were selected in the example above, then Request.Form("Companies") would
    return "3, 4"

    You will then have to insert the data in three steps.
    1) insert the person's information
    2)get the new person's ID
    3) insert the companies


    <%
    'Step One
    Dim sSQL
    Dim RS
    Dim PersonID
    Dim arrCompanies
    Dim iLoop

    sSQL="INSERT INTO People (FirstName, LastName) VALUES('Joe',
    'Blow')" 'Obviously you would use the values passed in

    CN.Execute sSQL 'I'm assuming CN is a properly opened connection
    object
    'Step Two
    Set RS=CN.Execute("SELECT @@IDENTITY") 'returns the most recently
    created Identity on this connection
    PersonID=RS.Fields(0).Value
    Set RS=nothing

    'Step Three
    arrCompanies=Split(Request.Form("Companies"),",") 'split takes a
    string and "splits" it into an array based on the delimiter (in this case a
    comma)

    For iLoop = 0 to UBound(arrCompanies)
    sSQL="INSERT INTO CompaniesPeopleJoin (PersonID, CompanyID)
    VALUES(" & PersonID & ", " & Trim(arrCompanies(iLoop)) & ")"
    CN.Execute sSQL
    Next

    %>

    Bear in mind, that if any of the inserts fail the previous ones would have
    succeeded so you would have only some of the values in.
    A better solution would use a transaction, and the best solution would use a
    Stored Procedure (Sql Server)



    "Christopher Brandsdal" <> wrote in message
    news:...
    > Ok. Thanks I understand.
    >
    > But the the problem is that both companies and persons are in the same
    > table...
    >
    > Should I change it to 2 different tables???
    >
    > and how will the code look like with the join command???
    >
    > When I add a person to the database I have to be able to select multiple
    > companies for that person...
    > (the companies are added in advance)
    >
    >
    >
    > "TomB" <> skrev i melding
    > news:...
    > > If you have two tables in your database, one called Companies and one

    > called
    > > People, you have to make a decision....
    > >
    > > If you just want to track a person's current company, and you are

    assuming
    > > that the people only work for one company at a time, then you add a
    > > CompanyID foreign key field to the People table. This is a one-to-many
    > > relationship with the company being on the "one" side and the People

    being
    > > the "many", thus one company has many people working for it.
    > >
    > > If you want to track a person's employment history, or allow for

    multiple
    > > companies then you need to create a many-to-many relationship-- meaning

    > one
    > > company has many people working for it, but also one person has worked

    (or
    > > is working) for many companies.
    > >
    > > To create the many-to-many relationship you create a "join" table, which
    > > contains foreign keys to both the Company table and the People table.
    > >
    > > Usually, the join table's primary key is the combination of the Foreign
    > > Keys. This prevents duplicate entries. There are times, however; when

    a
    > > duplicate entry would be acceptable. For example: if you are tracking

    an
    > > employment history, it could be possible for a person to work at one
    > > company, leave it, then come back to it. If you'd used the Foreign keys

    > as
    > > Primary key, then you wouldn't be able to add the return to the same
    > > company.
    > >
    > > Your idea to store the information in a single field (comma separated)

    is
    > a
    > > problematic solution. Besides being difficult to maintain and

    > parse....how
    > > would you decide how much data the field could hold?
    > >
    > >
    > >
    > >
    > > "Christopher Brandsdal" <> wrote in message
    > > news:...
    > > > Hi!
    > > >
    > > > I have a delicatg problem....
    > > > I have made a registration form for adding my friends information in a
    > > > database. The problem is that I want to connect the persons with

    > companies
    > > > in the same database. I wonder how i could connect a person to several

    > > other
    > > > id's in a table.
    > > >
    > > > I wonder if I can make a string in the database that holds all the

    id's
    > > > separated with , for example....?
    > > >
    > > > the string would then look like this:
    > > > 1,4,6,22,56
    > > > for example...
    > > >
    > > > But how can I then open this string and separate the numbers from the

    ,
    > > ???
    > > >
    > > > Hmm..
    > > > This may be difficult to ynderstand, but I have tried my best to

    explain
    > > ;)
    > > >
    > > > Christopher Brandsdal
    > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    TomB, Sep 4, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. EMW
    Replies:
    3
    Views:
    296
    Sharon
    Apr 17, 2004
  2. =?Utf-8?B?UnVkeQ==?=

    to store or not to store an image

    =?Utf-8?B?UnVkeQ==?=, Mar 29, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    640
    =?Utf-8?B?UnVkeQ==?=
    Mar 30, 2005
  3. =?Utf-8?B?TWFyaw==?=

    Where does VS2005 store csproj file info?

    =?Utf-8?B?TWFyaw==?=, Jan 7, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    2,480
  4. Renie83
    Replies:
    2
    Views:
    234
  5. Kenshin
    Replies:
    1
    Views:
    224
    Jeff Cochran
    Jun 25, 2004
Loading...

Share This Page