Put constraint on this

I have three tables:
1. SoftwareSupported,
2. VersionsSupported,
2. MappingTable (this one marries the two)
SoftwareSupported(SofwareID[PK], SoftwareName)
VersionSupported(VersionID[PK], SoftwareID[FK], VersionDesc)
MappingTable(AppID[PK], SoftwareID[PK], VersionID)
Facts state that:
1. Each application (appID) can support multiple software (SoftwareID) but
only one version at a time. So we could not have application X supporting
for example Photoshop versions 7 and 8 at the same. X can only support 7 OR
8.
I'm having a problem with this. It is possible to record (in the
MappingTable) combination of softwareID and VersionID which does not exist i
n
VersionsSupported. Therefore it is possible to specify that application X
supports Photoshop version 2.6 where in fact version 2.6 is MDAC's version,
not Photoshops. You can't enter bad info into VersionsSupported, but you ca
n
into MappingTable.
How can I ensure that this does not happen?Art,
1. define a unique constraint on VersionSupported(VersionID,
SoftwareID)
2. refer that unique constraint from a FK on MappingTable(VersionID,
SoftwareID)|||This design doesn't feel right. The fact that PhotoShop and MDAC can each
have a version 2.6 is coincidental. How about going with:
Software (SoftwareID[PK], SoftwareName)
SoftwareVersion (SoftwareID[PK], Version[PK])
Application (AppID[PK], AppName)
AppSoftware (AppID[PK], SoftwareID[PK], Version[PK])
You can have a FK between AppSoftware and SoftwareVersion on
(SoftwareID[PK], Version[PK]). Also, put a UNIQUE constraint on AppSoftware
(AppID[PK], Software[ID]). The entries in AppSoftware should contain only
the currently *supported* version of each software.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:491897DF-A61F-4912-A2DC-A2505B0B40C0@.microsoft.com...
I have three tables:
1. SoftwareSupported,
2. VersionsSupported,
2. MappingTable (this one marries the two)
SoftwareSupported(SofwareID[PK], SoftwareName)
VersionSupported(VersionID[PK], SoftwareID[FK], VersionDesc)
MappingTable(AppID[PK], SoftwareID[PK], VersionID)
Facts state that:
1. Each application (appID) can support multiple software (SoftwareID) but
only one version at a time. So we could not have application X supporting
for example Photoshop versions 7 and 8 at the same. X can only support 7 OR
8.
I'm having a problem with this. It is possible to record (in the
MappingTable) combination of softwareID and VersionID which does not exist
in
VersionsSupported. Therefore it is possible to specify that application X
supports Photoshop version 2.6 where in fact version 2.6 is MDAC's version,
not Photoshops. You can't enter bad info into VersionsSupported, but you
can
into MappingTable.
How can I ensure that this does not happen?|||Alex,
VersionID is a primary key in VersionSupported table. Even if the constraint
was implemented there is no way one could ever violate it (since VersionID
already makes it unique).
Look at this picture (http://www.hermesconsultinginc.com/drawing1.jpg), it
shows you the basic layout. Top table is the mapping table, middle table
lists all the software and the bottom one lists all the supported
versions/software.
The problem is that (someone with the knowledge of this database) could put
in the following row in the mapping table (the top one)
VersionID = 1, RequirementID = 5, SoftReqID = 2
This clearly is wrong since SoftReqID does not support VersionID = 1, it
only supports VersionID(s) between 4 and 7.
"Alexander Kuznetsov" wrote:

> Art,
> 1. define a unique constraint on VersionSupported(VersionID,
> SoftwareID)
> 2. refer that unique constraint from a FK on MappingTable(VersionID,
> SoftwareID)
>|||Tom,
You are absolutely right, Photoshop and MDAC could both have version 2.6
and this could be conincidental or actually intended. Users can define it
that way.
Please look at this picture
(http://www.hermesconsultinginc.com/drawing1.jpg) this shows you existing
design which is almost identical to what you suggested (I didn't include th
e
Application table in my discussion but it's there). Top table is the mapping
g
table, middle table is supported software and the bottom one is supporte
d
versions/software
The problem is that (someone with the knowledge of this database) could put
in the following row in the mapping table (the top one)
VersionID = 1, RequirementID = 5, SoftReqID = 2
This clearly is wrong since SoftReqID does not support VersionID = 1, it
only supports VersionID(s) between 4 and 7. This is what I was trying to
avoid via constraints (and I'm doing it in software right now)
"Tom Moreau" wrote:

> This design doesn't feel right. The fact that PhotoShop and MDAC can each
> have a version 2.6 is coincidental. How about going with:
> Software (SoftwareID[PK], SoftwareName)
> SoftwareVersion (SoftwareID[PK], Version[PK])
> Application (AppID[PK], AppName)
> AppSoftware (AppID[PK], SoftwareID[PK], Version[PK])
> You can have a FK between AppSoftware and SoftwareVersion on
> (SoftwareID[PK], Version[PK]). Also, put a UNIQUE constraint on AppSoftware
> (AppID[PK], Software[ID]). The entries in AppSoftware should contain only
> the currently *supported* version of each software.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:491897DF-A61F-4912-A2DC-A2505B0B40C0@.microsoft.com...
> I have three tables:
> 1. SoftwareSupported,
> 2. VersionsSupported,
> 2. MappingTable (this one marries the two)
> SoftwareSupported(SofwareID[PK], SoftwareName)
> VersionSupported(VersionID[PK], SoftwareID[FK], VersionDesc)
> MappingTable(AppID[PK], SoftwareID[PK], VersionID)
> Facts state that:
> 1. Each application (appID) can support multiple software (SoftwareID) bu
t
> only one version at a time. So we could not have application X supporti
ng
> for example Photoshop versions 7 and 8 at the same. X can only support 7
OR
> 8.
> I'm having a problem with this. It is possible to record (in the
> MappingTable) combination of softwareID and VersionID which does not exist
> in
> VersionsSupported. Therefore it is possible to specify that application X
> supports Photoshop version 2.6 where in fact version 2.6 is MDAC's version
,
> not Photoshops. You can't enter bad info into VersionsSupported, but you
> can
> into MappingTable.
> How can I ensure that this does not happen?
>|||Tom,
It works fine now, thanks for your help!
"Tom Moreau" wrote:

> This design doesn't feel right. The fact that PhotoShop and MDAC can each
> have a version 2.6 is coincidental. How about going with:
> Software (SoftwareID[PK], SoftwareName)
> SoftwareVersion (SoftwareID[PK], Version[PK])
> Application (AppID[PK], AppName)
> AppSoftware (AppID[PK], SoftwareID[PK], Version[PK])
> You can have a FK between AppSoftware and SoftwareVersion on
> (SoftwareID[PK], Version[PK]). Also, put a UNIQUE constraint on AppSoftware
> (AppID[PK], Software[ID]). The entries in AppSoftware should contain only
> the currently *supported* version of each software.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:491897DF-A61F-4912-A2DC-A2505B0B40C0@.microsoft.com...
> I have three tables:
> 1. SoftwareSupported,
> 2. VersionsSupported,
> 2. MappingTable (this one marries the two)
> SoftwareSupported(SofwareID[PK], SoftwareName)
> VersionSupported(VersionID[PK], SoftwareID[FK], VersionDesc)
> MappingTable(AppID[PK], SoftwareID[PK], VersionID)
> Facts state that:
> 1. Each application (appID) can support multiple software (SoftwareID) bu
t
> only one version at a time. So we could not have application X supporti
ng
> for example Photoshop versions 7 and 8 at the same. X can only support 7
OR
> 8.
> I'm having a problem with this. It is possible to record (in the
> MappingTable) combination of softwareID and VersionID which does not exist
> in
> VersionsSupported. Therefore it is possible to specify that application X
> supports Photoshop version 2.6 where in fact version 2.6 is MDAC's version
,
> not Photoshops. You can't enter bad info into VersionsSupported, but you
> can
> into MappingTable.
> How can I ensure that this does not happen?
>sql