store info in database.

  • Thread starter Christopher Brandsdal
  • Start date
C

Christopher Brandsdal

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
 
T

TomB

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?
 
J

Joseph Narissi

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
 
C

Christopher Brandsdal

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)
 
T

TomB

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)
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,832
Latest member
GlennSmall

Latest Threads

Top