December 20, 2006 at 6:54 am
Hi all,
I am a web developer whos been given the role of DBA. I know a fair bit of databasing but mostly from the client side where I am always writing interfaces. I need to make some changes to the schema of a database to accomodate a many-to-many relationship but I am unsure how to do it properly.
Here's the scenario...
There are certificates (products). Each cert can have several signatures (.gif files). The individual signatures may be used on several different certificates. The functionality required is for users to look up a certificate and see a list of the signatures it embodies. Also, it is necessary to search the signatures and see the list of certs for each.
I need help with this. I am running SQL Server 2K. There are pre-existing tables for both certs and sigs. I had the idea of using delimited lists stored in table columns but the problem here is there is alot of dynamic change. I appreciate any tips or advice very much.
Thanks
MR
December 20, 2006 at 8:04 am
MR
Create a table called CertsSigs. This will contain one row for every combination of certificate and signature. If you need a list of all certificates and their signatures, you can then join the Certificates, CertsSigs and Signatures tables.
John
December 20, 2006 at 8:50 am
Hi John,
This makes sense. On the interface end I could generate the CertSigs rows by making a new field on the add and update forms for the certificates. The new field can be a multi-select list of signatures available for that customer.
Then to retrieve the result for a certificate view I might use this:
FROM certificate JOIN certsigs
ON certsigs.certID = certificate.certID
and then replace the certificate table with the signature table for a signature view.
Sound reasonable?
Mike
December 20, 2006 at 9:09 am
Mike
Yes, that may show a list of SigIDs for all certificates that have one (or more). Use LEFT JOIN if you want to include certificates that don't have any signatures assigned. You'll need to join to Signatures if you want to resolve the SigIDs to their names, descriptions or whatever.
By the way, if you alias your tables, you will probably find your query is easier to read:
SELECT c.Description as Certificate, s.Description as Signature
FROM certificate c JOIN certsigs cs
ON c.certID = cs.certID
JOIN signature s
ON cs.sigID = s.sigID
John
December 20, 2006 at 9:42 am
Rgr on the signature join, I will need to display that info. Sweet, the aliases are a great idea!
It looks like all I need to really have in the new table is the signature ID, template ID and the customer ID. I'll start some experiments on the development copy of the DB and let you know how it goes later on.
Thanks!!
Mike
December 20, 2006 at 1:21 pm
Alright John,
After mucking around with my tables here is what I came up with:
SELECT LastName
FROM TempSig TS JOIN TEMPLATE T
ON TS.TemplateID = T.TemplateID
JOIN SIGNATURE S
ON TS.sigID = S.SignatureID
WHERE T.Description = 'Quality Inspection'
result:
Vondrell
Jackson
Newrock
A list of signatures by last name for the Quality Inspection certificate. I do wish the database designer hadn't used capitalized table names - makes it messy looking. Works like a charm though. The new table also has the field for customer ID so I can generate reports on all the certificates for a certain customer with signatures having the first name Joe, etc. etc.
Now that I have it working properly it's hard to imagine I had difficulty thinking it up in the first place. It seems so obvious now. I guess that's what makes a noob a noob eh?
Much appreciation to you for the tips and examples!!!! Happy holidays to you if you celebrate any!
Mike
December 21, 2006 at 1:35 am
Mike
No worries. That kind of linking table is called a junction table, and it's the kind of database design aspect that would have taken me ages to think up on my own if I hadn't read about it a few years ago!
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply