Database Architecture - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Architecture

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-20 : 08:27:17

Kelly Brady writes "Database Architecture

I am trying to come up with the database architecture to do the following.

Here is my form page that I use to insert records into my database:

<form name="AddDiscussion" method="post" action="addprocess.asp">

      <select name="Issue">
 	<option value=”1”>PC</option>
<option value=”2”>Software</option>
        </select>

        <select name="GRIPA_Attendees" size="3" multiple>
          <option value=”1”>Frank Perdue</option>
          <option value=”2”>Bill Walldale</option>
          <option value=”3”>Jesus Christ</option>
        </select>
        
        <select name="VIA_Attendees" size="3" multiple>
          <option value=”1” >Bob Hope</option>
          <option value=”2” >Vince Gill</option>
          <option value=”3” >Luke Perry</option>
          <option value=”4” >Davey Crockit</option>
        </select>

        <textarea name="Primary_Discussion" cols="35" rows="3"></textarea>

        <select name="Status">
          <option value=”1”>Approved</option>
          <option value=”2”>Dis-Approved</option>
          <option value=”3”>Pending</option>
        </select>
    <input type="submit" name="Submit" value="Submit">
  <input type="reset" name="Submit2" value="Reset">
  
</form>

Notice the form fields Select GRIPA_Attendees and VIA_Attendees. I want the user to be able to select more than one name in these two fields. I have some ideas on how my database should look like however I am having difficulties trying to figure out how I will perform multiple table inserts. I want to try and perform inserts in the database using a user function within SQL Server 2000. Here are some more ideas I had:

Table 1) Create table one that is the primary table for the records to go into
Table 2) Create another table for GRIPA_Attendees (fname, lname, email, and ID)
Table 3) Create another table for Via_Attendees (fname, lname, email, and ID)

For <select name="GRIPA_Attendees" I will loop through the records in Table 2 and display the name and use ID as the value

For <select name="Via_Attendees" I will loop through the records in Table 3 and display the name and use ID as the value

I have also created other tables to do the same for <select name="Status">, <select name="Issue">

The issue becomes when I try and insert a record into table 1 and I make multiple selections on either of the selects that allow multiples. Why? Well to start I will have a record that inserts id’s for either of the multiple selects:

1,2,3

Now when I try and display the data later on using a join on both table 2 and 3 in order to also retrieve fname, lname, email from both tables it will bomb unless I create some vb to look at each ID (1,2,3) and then get that data. It really seams quite teediest rather then trying to come up with a better way to insert the data using a cross reference table and performing the entire insert process based on a SQL User Function (TSQL)

The thought behind this idea would be to create 2 more additional table that would be the cross reference between my Primary table (using its unique key) and table 2 and 3 (using there unique keys). To look something like this:

Primary Table
ID int(4) Increment by one | Sub_ViaID | Sub_GRIPAID | etc………
1 |1 |1

Cross Reference table between primary and table 2
Sub_GRIPAID | GRIPAID
1 |1
1 |2

Cross Reference table between primary and table 3
Sub_GRIPAID | GRIPAID
1 |1
1 |2

So the question becomes
1) How do I insert into tables 2 and 3 if multiple selection have been made in order to generate the unique key for the cross-reference tables and for inserting

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-05-20 : 08:27:17

And we truncated at 4,000 characters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources