CODEWITHSUNDEEP
c#asp.netvisual-studiosql-server-2008
Ace Troubleshooter
Ace Troubleshooter

Reputation: 1379

String together several values to insert into column?

I've got a logic-oriented issue, I think. I'm restructuring the database on my site to make it useful to the end user, and it's looking like this: I've got a list of seven products available, and each of them have an id of 1-7. I've got a submission table with an auto-incremented Id property (SubmissionId) to identify each unique submission, which may have multiple products (for example, a new customer may submit an add product form with EPL, Crime Fidelity, and Fiduciary), but not redundant products (can't have EPL and another EPL). I need to be able to link the product Id's to each submission in a workable way so that I can call it up to display the relevant information when a user calls up the submission to review it (basically like looking at their shopping cart). Obviously it's not feasible to create a table with all the possible iterations of 1-7 when there aren't necessarily 7 digits in the final count (because otherwise there would only be 49), so I was wondering if there was a way to combine each product id into a string, or something like it, insert that number into the ProductId column of the Submission table, and then use a SELECT WHERE LIKE statement to call up all the products associated with that particular SubmissionId?

For example, a user submits a new form, and the database autoincrements the value for column SubmissionId as 8. Then they add four products; EPL, Crime Fidelity, Fiduciary and Professional D&O. Those products have respective values of 1,3,4 and 6 in the column "ProductId" in the Product table. The collective ProductId value to be inserted into the Submission table would be 1346, and the logic to display the data associated with SubmissionId 8 would have a switch case statement looping through that ProductId to see if it contained each of the numbers 1-7 using the SELECT WHERE ProductId is LIKE (number), and then displaying the data from the relevant cases. How would I get the "1346" value to insert into the Submission table's ProductId column?

Upvotes: 0

Views: 168

Answers (1)

empi
empi

Reputation: 15901

You should add a table that would link Product with Submission, eg. table S2P { Id, ProductId, SubmissionId } (I can't come up with better name). This is standard way to implement many to many relationship.

Check this link: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php or google for many to many relationship to get more information.

Upvotes: 1

Related Questions