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

[CodeMonkey]

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.

 

[Jeff Boyce]

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

 

[Larry Daugherty]

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

HTH

 

[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

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.

 

[Jeff Boyce]

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

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

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.

 

[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

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

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 Boyce]

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


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

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

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

Jeff Boyce
Microsoft Office/Access MVP

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

 

[CodeMonkey]

Jeff
thanks very much for all your help.

Regards
CodeMonkey.