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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-15 : 09:33:51
|
| Doug writes "SQLTeam,Thanks for all the help! Keep up the EXCELLENT work. Enough kiss-butt, to the point.I could be way off base with the design, but this is what I want to do.1) I have built a table to store ALL of the image LINKS (not binary data) and attributes for all of the classified advertisements and advertisers.CREATE TABLE tblImage ([imageid] [int] NOT NULL IDENTITY(1,1),[filename] [varchar] (255) NOT NULL,[width] [int] NULL,[height] [int] NULL,[border] [int] NULL,[alt] [varchar] (50) NULL,[onclick] [ntext] NULL,[onmouseover] [ntext] NULL,[onmouseout] [ntext] NULL,[css_class] [varchar] (50) NULL,[create_date] [datetime] NOT NULL)2) Information about advertisers is stored in the tblAdvertiser table. Each advertiser can have many images. So, I have a one to many relationship between tblAdvertiser and tblImage.3) Information about advertisements is stored in the tblAdvertisement table. Each advertisement can have many images. So, I have a one to many relationship between tblAdvertisements and tblImage.4) In the future, I may store images for other entities in tblImage, creating an additional one to many relationship.Is it fundamentally incorrect to use tblImage to store images that link to different entities? Can I use link tables?CREATE TABLE tblAdvertiser_tblImage ([advertiserid] [int] NOT NULL,[imageid] [int] NOT NULL)CREATE TABLE tblAdvertisement_tblImage ([advertisementid] [int] NOT NULL,[imageid] [int] NOT NULL)If I can use link tables, can I insert a record into tblImage and into the link table using a single SQL statement and return the IDENTITY to my VB component?Any suggestions or design expertise is much appreciated.Best regards,DougP.S. You guys really are SQL Experts! :)" |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-15 : 21:28:33
|
| Doug,Your design seems OK, and it' OK to use the images table for all your images, if the table isn't huge. If it's so big that it slows you down, you might want separate tables.To insert and return the imageid in one step, you might consider doing a stored proc which does the inserts, and then Select MAX(imageid) from tblimage will give you the latest added row's imageid. You can return this an output param or as the return code if you're not concerned about errors. In VB/ADO, this is very simple with a command object.Sarah Berger MCSD |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-16 : 12:06:19
|
quote:
CREATE TABLE tblAdvertiser_tblImage ( [advertiserid] [int] NOT NULL, [imageid] [int] NOT NULL ) CREATE TABLE tblAdvertisement_tblImage ( [advertisementid] [int] NOT NULL, [imageid] [int] NOT NULL )
hmm.. You got the right idea.. but a little backwards. now it seems you want 1 company to many ads and 1 ad to many images (would make sense for it to be this way).but the way you currently setup it looks like you to go 1 image to many companies and 1 image to many ads.now I'm not quite sure about the qualifications of it.. so before I go too far into it tell me one thing.. can a company have images that don't belong to an advertisement?email me if you want... I'll be able to answer you a little faster.Heh, it's amazing the free time I got right now.. just watching this 2.5 hour dts package run ;) |
|
|
|
|
|
|
|
|
Add record, return identity, add link for many-to-many in same statement