Basic modeling question

Hi,
I have a simple design problem that I hope someone can help me with.
Assume we have the following tables:
Product
--
ProductID (PK)
Name
Order
--
OrderID (PK)
Date
Attachment
--
AttachmentID
Name
Both table Product and Order can have one or more attachments. If I create
an table called
ProductAttachment
--
ProductID (PK)
AttachmentID (PK)
and one called OrderAttachment I will have a problem where the PK in
"Attachment" will reference both ProductAttachment and OrderAttachment. My
main problem is that we have quite a few tables that can have attachments an
d
I want a design where I don't have to add new columns when new tables that
require attachments are added. I want attachment information to be stored in
one central table.
Any good ideas? Btw, please let me know if you have any tips on sites that
go through database modeling techniques.
Thanks in advance,
MansoManso wrote:
> Hi,
> I have a simple design problem that I hope someone can help me with.
> Assume we have the following tables:
> Product
> --
> ProductID (PK)
> Name
> Order
> --
> OrderID (PK)
> Date
> Attachment
> --
> AttachmentID
> Name
> Both table Product and Order can have one or more attachments. If I create
> an table called
> ProductAttachment
> --
> ProductID (PK)
> AttachmentID (PK)
> and one called OrderAttachment I will have a problem where the PK in
> "Attachment" will reference both ProductAttachment and OrderAttachment. My
> main problem is that we have quite a few tables that can have attachments
and
> I want a design where I don't have to add new columns when new tables that
> require attachments are added. I want attachment information to be stored
in
> one central table.
"I want" is hardly a formal design criteria and is likely to tie you in
all sorts of knots if you aren't careful. I don't necessarily see
anything wrong with the table structure you sketched out. The name
"Attachment" does sound suspiciously like some sort of weakly-typed
property bag arrangement, which could be very nasty - but then I could
be completely mistaken since I've only got your names to go on.
If you think it helps you could create a parent of Products and Orders
and reference that in your many-to-many association table. I'd be very
dubious about the validity of some super entity that encompassed both
Orders and Products however. The fact that it seems to be called for
here is causing me to see the warning letters "OTLT!" written in
flashing red lights over the Attacment table.
http://tonyandrews.blogspot.com/200...ibrary/ms130214(en-US,SQL.90).aspx
--|||David, thanks for taking the time to help me out.
I know this sounds like a "two table solution" but that's not what I'm
trying to do. Attachment is a table used to track file attachments. Many
object can have attachments and I want to try and avoid to create one
attchment table for each type.
A stupid question. If I have one "Attachment" table and two lookup tables
(ProductAttachment/OrderAttachment) RI doesn't allow me to reference both.
How can I design that? I guess that's my question. Or, should I add FK
columns to the Attachment table instead (i.e. ProductID, OrderID) and use
null values and check constraints?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> "I want" is hardly a formal design criteria and is likely to tie you in
> all sorts of knots if you aren't careful. I don't necessarily see
> anything wrong with the table structure you sketched out. The name
> "Attachment" does sound suspiciously like some sort of weakly-typed
> property bag arrangement, which could be very nasty - but then I could
> be completely mistaken since I've only got your names to go on.
> If you think it helps you could create a parent of Products and Orders
> and reference that in your many-to-many association table. I'd be very
> dubious about the validity of some super entity that encompassed both
> Orders and Products however. The fact that it seems to be called for
> here is causing me to see the warning letters "OTLT!" written in
> flashing red lights over the Attacment table.
> http://tonyandrews.blogspot.com/200...es.htm
l
> But then again, I could still be wrong.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You also newed to learn how to name data elements. "name" by itself is
not a valid data element -- it has to be the name ()date, weight, size,
type, etc.) of SOMETHING in PARTICULAR.
WHY' Are these magically, undefined attachments **logiically** the
same? Could you actually define what they are?
go through database modeling techniques <<
Spend about 6-12 months in a solid college-level course on data
modeling. You are not going to get an education off of the Internet
newsgroups.