Hi there!
Currently I've managed to etablish relationsships on fields to/from
different tables. But the 'visual' result is a bit off from what I wish.
Currently MSSQL diagram designer hooks up the relations from table to table.
I would very much like to change that to 'field to field' instead, as to
make it easier to visually determine relationsships. Or alternative get
MSSQL to automatically change the shown labels to say 'id = catID'.
What options do I have regarding this?
I doubt, therefore I might be.
a different tool
ErWin or ER-Studio are what I recommend
Greg Jackson
PDX, Oregon
|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23aR2GdzVFHA.2700@.TK2MSFTNGP12.phx.gbl
> ErWin or ER-Studio are what I recommend
Thanks for the recommendation, however it seems that it costs money, and
because I'm not going to use this kind of tool very often (maybe 5-10 times
a year), I really can't justify paying for this. Is there any free tool on
the market, that is easy to use? I only need a few things to visualize.
I doubt, therefore I might be.
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Thursday, March 8, 2012
Database diagram
Hi all,
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
Luigi
Unfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
Luigi
Unfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
Database diagram
Hi all,
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
LuigiUnfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
LuigiUnfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
Wednesday, March 7, 2012
Database diagram
Hi all,
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
LuigiUnfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
is it possible (with SS2005) to create a database diagram with the
arrows near the tables' fields that have a relation?
With my SQL Server 2005 database diagram, the arrows are placed
casually, not near the fields that are related with other tables.
Thanks in advance.
LuigiUnfortunately you have to jigger the arrows around by hand--drag the
ends to the fields where they belong. It takes a little time (and can
be exasperating when you end up with little loops) but it can be done.
--Mary
On 22 Feb 2007 06:34:25 -0800, "ciupaz" <luigi.zambetti@.gmail.com>
wrote:
>Hi all,
>is it possible (with SS2005) to create a database diagram with the
>arrows near the tables' fields that have a relation?
>With my SQL Server 2005 database diagram, the arrows are placed
>casually, not near the fields that are related with other tables.
>Thanks in advance.
>Luigi
Sunday, February 19, 2012
database design - keys
Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
an
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
a
the
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
for
commonly
favor
generally
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
quote:
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
quote:|||There are no general rules or norms which recommend a specific datatype for
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
quote:
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
quote:
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
quote:|||Thanks for your input Anith.
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
have[QUOTE]
> an
called[QUOTE]
>
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
quote:
> There are no general rules or norms which recommend a specific datatype
for
quote:
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
quote:
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
quote:
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
quote:
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>
database design - keys
Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
--
Anith|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> > Hello All
> >
> > I am designing a database, or rather redesigning a very old database and
> > have a question regarding setting up key fields. The old database has a
> > table called Equipment with two fields:
> >
> > Equipment Code - text 8
> > Equipment Description - text 50
> >
> > In the new design I will have a table called Manufacturers that will
have
> an
> > Equipment Code field, which will link to the Equipment table to get the
> > description. My question is, should I make a new Integer key field
called
> > say EquipID which is what would get stored in the Manufacturers table or
> > should I simply use the Equipment Code field as the key? What are the
> > advantages/disadvantages to each method?
> >
> > I have several other tables with a similar situation where the 'Code' is
> > unique but there are more fields in these tables.
> >
> > Thanks,
> > Gerry
> >
> >
>|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> There are no general rules or norms which recommend a specific datatype
for
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>|||Thanks Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> >--Original Message--
> >Hello All
> >
> >I am designing a database, or rather redesigning a very
> old database and
> >have a question regarding setting up key fields. The
> old database has a
> >table called Equipment with two fields:
> >
> >Equipment Code - text 8
> >Equipment Description - text 50
> >
> >In the new design I will have a table called
> Manufacturers that will have an
> >Equipment Code field, which will link to the Equipment
> table to get the
> >description. My question is, should I make a new
> Integer key field called
> >say EquipID which is what would get stored in the
> Manufacturers table or
> >should I simply use the Equipment Code field as the
> key? What are the
> >advantages/disadvantages to each method?
> >
> >I have several other tables with a similar situation
> where the 'Code' is
> >unique but there are more fields in these tables.
> >
> >Thanks,
> >Gerry
> >
> >
> >.
> >
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
--
Anith|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> > Hello All
> >
> > I am designing a database, or rather redesigning a very old database and
> > have a question regarding setting up key fields. The old database has a
> > table called Equipment with two fields:
> >
> > Equipment Code - text 8
> > Equipment Description - text 50
> >
> > In the new design I will have a table called Manufacturers that will
have
> an
> > Equipment Code field, which will link to the Equipment table to get the
> > description. My question is, should I make a new Integer key field
called
> > say EquipID which is what would get stored in the Manufacturers table or
> > should I simply use the Equipment Code field as the key? What are the
> > advantages/disadvantages to each method?
> >
> > I have several other tables with a similar situation where the 'Code' is
> > unique but there are more fields in these tables.
> >
> > Thanks,
> > Gerry
> >
> >
>|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> There are no general rules or norms which recommend a specific datatype
for
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>|||Thanks Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> >--Original Message--
> >Hello All
> >
> >I am designing a database, or rather redesigning a very
> old database and
> >have a question regarding setting up key fields. The
> old database has a
> >table called Equipment with two fields:
> >
> >Equipment Code - text 8
> >Equipment Description - text 50
> >
> >In the new design I will have a table called
> Manufacturers that will have an
> >Equipment Code field, which will link to the Equipment
> table to get the
> >description. My question is, should I make a new
> Integer key field called
> >say EquipID which is what would get stored in the
> Manufacturers table or
> >should I simply use the Equipment Code field as the
> key? What are the
> >advantages/disadvantages to each method?
> >
> >I have several other tables with a similar situation
> where the 'Code' is
> >unique but there are more fields in these tables.
> >
> >Thanks,
> >Gerry
> >
> >
> >.
> >
database design - joins on which fields?
Hi,
I,m trying to develop a relational database but confused so much & need help to streamline the basics of the the database design error free.
Below is the case,
There r six databases with the joined fileds as below,
Buyer( filed used to join with style / po / production / shipped = Buyer Name )
Factory ( filed used to join with style / po / production / shipped = Factory Name )
Style ( one style have multiple pos ) ( filed used to join with po / production / shipped = Style # )
Po ( one po have multiple styles )( filed used to join with production / shipped = Po # )
Production
Shipped
There is nothing unique in all the 6 databases to make any filed primary key.
If in Buyer database, if we change the buyer name then all the records ralated to this buyer disturbed in all the databases & same is the case for factory name, style # & po # in Factory / Style & PO databases & any of these changes in first 4 databases ( i.e Buyer / Factory / Style / Po ) also badly effects the record linking & records in Production & Shipped databases.
Need advice how to join the databases in this case?
Is I use the auto serial # method in all the 6 databases or is there other ways to do so?
Will appreciate if someone let me know the way or guide me on this?
Thanks / Sulman
Sorry, but due to this unclear information given, I think it is hard to find a quick solution for you, perhaps you post some DDL and sample data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Database Design - Boolean Fields
I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes.
Which method of design would you chose for this scenario:
- Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joining
So second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records.
Thanks
Ben
Can you post some sample data so I can "see" what you mean..
|||For example, say I have a table of homes. The homes table will have general attributes that all home objects share (address, sq ft, price) but then there are optional boolean fields that are attributes of homes (Is TwoStory, HasGasStove, HasAlarmSystem).
So would you store these boolean fields in the homes table, or create a table of attributes and a relationship table?
The first choice would have the potential for many null values in the table for each record. Most likely this table would not be normalized.
The second option would normalize the table, but suffer from the performance penalty of the joining 3 tables (tbl_homes, tbl_homeattributes, tbl_relationship_homes_attributes) to get the information. There would not be any null values though.
This situation could apply to any object that has boolean attributes: clothes, cars, computers, buildings.
Thanks
Ben
I would create an Attributes lookup table with an AttributeId mapped to an attribute
AttribId AttribName
---------
1 IsTwoStory
2 HasGasStove
3 HasAlarmSystem
Then there would be a cross-reference table with a Home mapped to all its atributes.
HomeId AttribId
------
1 1
1 2
2 1
2 2
2 3
3 1
this way, if any new attributes get added in future you just add them to the attributes table and if there are homes that match that attribute you add them to the cross ref table. Similarly if existing attributes get removed or if a home loses some attributes you just update the cross ref table appropriately. This approach will give you a properly normalized structure with best scalability.
Subscribe to:
Posts (Atom)