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.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2007-11-08 : 10:29:55
|
| Hi,I'm struggling coming up with an optimal table design for the following scenario.I have a table in which each row is recording information about a document - author, publisher, publication, title, abstract and so forth. I also have an image table which contains information about the images which can be associated with the documents.Each document can have on of three image types associated with it - an author photo, a publisher logo and a publication logo. There aren't actually that many different authors, publishers and publications in the database so it would be common for many documents to have the same author or the same publisher although authors sometimes write for different publications. In the current setup it's possible for a document to have more than one image of each type associated with it, which is undesirable - this needs to eliminated and we need to ensure that each document can only have one image of each type associated with it.It'd also be a huge boon if I could get all the information about a document and it's attached images with a single database call. In the current setup, trying to do this returns multiple rows so I've ended up making two calls, one for the document and one for it's attached images.I can see two possible ways of setting this up, neither of which appeal. The first is to add three columns to my document table to record the image URLs which is simple but inflexible should I want to add more image types later on.The second is to set up three new tables for author, publisher and publication and associate each of these with the document table through a key and then to the image table through another key. There's also an image type table which is going to come in to play. I'm unwilling to do this because it's going to just explode the number of tables I've got, especially if we add more image types, and play havoc with my XML schemas. Is there another way of rationalizing this that I've missed?Cheers,Matt |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-12 : 09:43:50
|
| if you could post a current model it would be helpfulif the fields on the different images are basically the same, here is a guesstblDocument PK DocID, tblType PK TypeID, tblDocumentType PK DocID, TypeIDfor the result set, somthing likeSELECT Doc.*,Photo.*,Logo.*,Icon.*FROM tblDocumentLEFT JOIN tblDocumentType AS Photo ON tblDocumentType.DocID = tblDocument.DocID AND tblDocumentType.TypeID = 1--PHOTOLEFT JOIN tblDocumentType AS Logo ON tblDocumentType.DocID = tblDocument.DocID AND tblDocumentType.TypeID = 2--LOGOLEFT JOIN tblDocumentType AS Logo ON tblDocumentType.DocID = tblDocument.DocID AND tblDocumentType.TypeID = 3--ICON |
 |
|
|
TimmyC
Starting Member
10 Posts |
Posted - 2007-11-12 : 10:03:27
|
| Document:DocIDAuthorIDPublisherIDPublicationIDTitleetcAuthor:AuthorIDAuthorNameetcAuthorImagePublishers:PublisherIDPublisherNameetcPublisherImagePublications:PublicationIDetcPublicationImageWith this design you can even use an inner join between the tables to get 1 record out (if a document has 1 author, 1 publisher etc).Do you really need to have an image table? Does an author really have more than 1 image of themselves? Or a publisher more than 1 logo?If so you COULD add another record in the table representing the same publisher... but thats not very Normal.Hope this helps. |
|
|
|
|
|
|
|
|
Best table design