Table design question - Do I need to split this table up?

Hi all
I have the following fields in a single table with no relationships to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.

Why have two tables? If your Table1 has a field for
[SoftwareReleaseNumber], you could put version 1 and version 2 in the same
table.

When you find you are using "repeating fields", typically containing data as
part of the fieldnames (e.g., LowXXXX, MediumXXXX, HighXXXX), you are using
Access like a spreadsheet.

You (and Access) will have to work extra hard to do what you want with that
data design (Access' features and functions are designed for use with
well-normalized data).

You seem to be describing a "one-to-many" relationship (one Software Release
can have one-to-many "Weightings"). The relational way to handle this is
with a table to hold Software Release-specific info, and a second table to
hold "Weighting" information related to the Release.

That second table might look something like:

trelReleaseWeighting
ReleaseWeightingID
ReleaseID (a foreign key pointing back to the primary key in the
Release table)
WeightID (I'll suggest a tlkpWeight table, so you are not limited to
Low, Medium, High)
ReleaseWeightValue (this is what you've been putting in your table1
and table2)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message
ps.com...
Hi all
I have the following fields in a single table with no relationships to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.

What you have described would work nicely as the many side of a
one-to-many relationship with tblSoftware on the one side.

HTH
--
-Larry-
--

"CodeMonkey" wrote in message
ps.com...
Hi all
I have the following fields in a single table with no relationships
to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is
no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.

On 11 Oct, 22:51, "Jeff Boyce" wrote:
Why have two tables? If your Table1 has a field for
[SoftwareReleaseNumber], you could put version 1 and version 2 in the same
table.

When you find you are using "repeating fields", typically containing data as
part of the fieldnames (e.g., LowXXXX, MediumXXXX, HighXXXX), you are using
Access like a spreadsheet.

You (and Access) will have to work extra hard to do what you want with that
data design (Access' features and functions are designed for use with
well-normalized data).

You seem to be describing a "one-to-many" relationship (one Software Release
can have one-to-many "Weightings"). The relational way to handle this is
with a table to hold Software Release-specific info, and a second table to
hold "Weighting" information related to the Release.

That second table might look something like:

trelReleaseWeighting
ReleaseWeightingID
ReleaseID (a foreign key pointing back to the primary key in the
Release table)
WeightID (I'll suggest a tlkpWeight table, so you are not limited to
Low, Medium, High)
ReleaseWeightValue (this is what you've been putting in your table1
and table2)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

ps.com...



Hi all
I have the following fields in a single table with no relationships to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
thanks for the response. However using your model, I am missing data
for FeatureType. FeatureType is not just a field that I inserted as a
PK was required, it needs to be datafilled. I have FeatureTypes like
"MyFeature", "MyNewFeature", etc that need be included. It looks to me
like I need another lookup table to store this. The table design would
look like this:

tlkpWeight -

WeightID [PK] WeightType


trelWeighting -

ReleaseWeightingID [PK] FeatureTypeID [FK] ReleaseID [FK] WeightID
[FK] ReleaseWeightValue

tblRelease - (question, should this really be called tlkpRelease as
its another lookup table?)

ReleaseID [PK] Release

AND THE NEW TABLE:

tlkpFeaturetype

FeatureTypeID [PK] FeatureType

Does this look right?

Regards
CodeMonkey.

I regularly use lookup tables to provide a limited set of "correct"
responses. This prevents users from getting ... "creative" or guessing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message
oups.com...
On 11 Oct, 22:51, "Jeff Boyce" wrote:
Why have two tables? If your Table1 has a field for
[SoftwareReleaseNumber], you could put version 1 and version 2 in the
same
table.

When you find you are using "repeating fields", typically containing data
as
part of the fieldnames (e.g., LowXXXX, MediumXXXX, HighXXXX), you are
using
Access like a spreadsheet.

You (and Access) will have to work extra hard to do what you want with
that
data design (Access' features and functions are designed for use with
well-normalized data).

You seem to be describing a "one-to-many" relationship (one Software
Release
can have one-to-many "Weightings"). The relational way to handle this is
with a table to hold Software Release-specific info, and a second table
to
hold "Weighting" information related to the Release.

That second table might look something like:

trelReleaseWeighting
ReleaseWeightingID
ReleaseID (a foreign key pointing back to the primary key in the
Release table)
WeightID (I'll suggest a tlkpWeight table, so you are not limited
to
Low, Medium, High)
ReleaseWeightValue (this is what you've been putting in your
table1
and table2)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

ps.com...



Hi all
I have the following fields in a single table with no relationships to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
thanks for the response. However using your model, I am missing data
for FeatureType. FeatureType is not just a field that I inserted as a
PK was required, it needs to be datafilled. I have FeatureTypes like
"MyFeature", "MyNewFeature", etc that need be included. It looks to me
like I need another lookup table to store this. The table design would
look like this:

tlkpWeight -

WeightID [PK] WeightType


trelWeighting -

ReleaseWeightingID [PK] FeatureTypeID [FK] ReleaseID [FK] WeightID
[FK] ReleaseWeightValue

tblRelease - (question, should this really be called tlkpRelease as
its another lookup table?)

ReleaseID [PK] Release

AND THE NEW TABLE:

tlkpFeaturetype

FeatureTypeID [PK] FeatureType

Does this look right?

Regards
CodeMonkey.

On 12 Oct, 20:17, "Jeff Boyce" wrote:
I regularly use lookup tables to provide a limited set of "correct"
responses. This prevents users from getting ... "creative" or guessing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

oups.com...



On 11 Oct, 22:51, "Jeff Boyce" wrote:
Why have two tables? If your Table1 has a field for
[SoftwareReleaseNumber], you could put version 1 and version 2 in the
same
table.

When you find you are using "repeating fields", typically containing data
as
part of the fieldnames (e.g., LowXXXX, MediumXXXX, HighXXXX), you are
using
Access like a spreadsheet.

You (and Access) will have to work extra hard to do what you want with
that
data design (Access' features and functions are designed for use with
well-normalized data).

You seem to be describing a "one-to-many" relationship (one Software
Release
can have one-to-many "Weightings"). The relational way to handle this is
with a table to hold Software Release-specific info, and a second table
to
hold "Weighting" information related to the Release.

That second table might look something like:

trelReleaseWeighting
ReleaseWeightingID
ReleaseID (a foreign key pointing back to the primary key in the
Release table)
WeightID (I'll suggest a tlkpWeight table, so you are not limited
to
Low, Medium, High)
ReleaseWeightValue (this is what you've been putting in your
table1
and table2)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

oups.com...

Hi all
I have the following fields in a single table with no relationships to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
thanks for the response. However using your model, I am missing data
for FeatureType. FeatureType is not just a field that I inserted as a
PK was required, it needs to be datafilled. I have FeatureTypes like
"MyFeature", "MyNewFeature", etc that need be included. It looks to me
like I need another lookup table to store this. The table design would
look like this:

tlkpWeight -

WeightID [PK] WeightType

trelWeighting -

ReleaseWeightingID [PK] FeatureTypeID [FK] ReleaseID [FK] WeightID
[FK] ReleaseWeightValue

tblRelease - (question, should this really be called tlkpRelease as
its another lookup table?)

ReleaseID [PK] Release

AND THE NEW TABLE:

tlkpFeaturetype

FeatureTypeID [PK] FeatureType

Does this look right?

Regards
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
right but does my updated model look ok with the new lookup table AND
the new FeatureTypeID field in trelWeighting?

Thanks
CodeMonkey

I'm with you on changing the prefix/name on that table which is "another
lookup table".

The way I'd check to see if I had tables laid out the way I needed would be
to diagram my entities and relationships on paper. Can I show each table as
an entity? Does it have a way to relate to the other table(s) it needs to?
I'll suggest that you're in a better position to determine that than I am --
you know your subject matter/domain better than I do.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"CodeMonkey" wrote in message
oups.com...
On 12 Oct, 20:17, "Jeff Boyce" wrote:
I regularly use lookup tables to provide a limited set of "correct"
responses. This prevents users from getting ... "creative" or guessing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

oups.com...



On 11 Oct, 22:51, "Jeff Boyce" wrote:
Why have two tables? If your Table1 has a field for
[SoftwareReleaseNumber], you could put version 1 and version 2 in the
same
table.

When you find you are using "repeating fields", typically containing
data
as
part of the fieldnames (e.g., LowXXXX, MediumXXXX, HighXXXX), you are
using
Access like a spreadsheet.

You (and Access) will have to work extra hard to do what you want
with
that
data design (Access' features and functions are designed for use with
well-normalized data).

You seem to be describing a "one-to-many" relationship (one Software
Release
can have one-to-many "Weightings"). The relational way to handle
this is
with a table to hold Software Release-specific info, and a second
table
to
hold "Weighting" information related to the Release.

That second table might look something like:

trelReleaseWeighting
ReleaseWeightingID
ReleaseID (a foreign key pointing back to the primary key in
the
Release table)
WeightID (I'll suggest a tlkpWeight table, so you are not
limited
to
Low, Medium, High)
ReleaseWeightValue (this is what you've been putting in your
table1
and table2)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CodeMonkey" wrote in message

oups.com...

Hi all
I have the following fields in a single table with no relationships
to
other tables:

FeatureType[PK] LowWeightingValue MediumWeightingValue
HighWeightingValue

A given FeatureType value may not have all values for all three
Weights, but then again another FeatureType may have all three
Weights.

The above table is for Software Release 1.

When I want to add data for Software Release 2, I create a second
table with the same fields and datafill this second table. There is
no
relationship created between table 1 and table 2.

Is there a better database design than this? Any help is really
appreciated.

Thanks
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
thanks for the response. However using your model, I am missing data
for FeatureType. FeatureType is not just a field that I inserted as a
PK was required, it needs to be datafilled. I have FeatureTypes like
"MyFeature", "MyNewFeature", etc that need be included. It looks to me
like I need another lookup table to store this. The table design would
look like this:

tlkpWeight -

WeightID [PK] WeightType

trelWeighting -

ReleaseWeightingID [PK] FeatureTypeID [FK] ReleaseID [FK] WeightID
[FK] ReleaseWeightValue

tblRelease - (question, should this really be called tlkpRelease as
its another lookup table?)

ReleaseID [PK] Release

AND THE NEW TABLE:

tlkpFeaturetype

FeatureTypeID [PK] FeatureType

Does this look right?

Regards
CodeMonkey.- Hide quoted text -

- Show quoted text -

Jeff
right but does my updated model look ok with the new lookup table AND
the new FeatureTypeID field in trelWeighting?

Thanks
CodeMonkey

Jeff
thanks very much for all your help.

Regards
CodeMonkey.