database diagrammer bug??

This morning I spent 3 hours drawing a diagram for 75 tables, had it looking
very nice, was saving on a regular basis, but then I couldn't save data
structure changes to a table until I closed the diagram out, which also
wanted to save one last time. So when I opened it back up, nearly all of my
work was gone. Tables that I had removed are now back. Objects are not
arranged like I had them, they're all in a straight line. All relationships
except for 4 or 5 are gone. I pretty much wasted all that time. Is this a
bug?
I am having other Enterprise Manager problems too, like I cannot see bit
fields in my available fields list while creating indexes. I would think
that I should reinstall Enterprise Manager but I've reinstalled twice with
the client tools on sql2k and still have the bit field problem.
thank you,
CoryI have not used diagraming in sql server enterprise manager as extensively a
s
you have. In general it is not a good tool for creating the database diagram
.
It is good for a few tables.
You should consider using a tool that was properly built for diagraming.
Like ErWin, I even Use Visio Professional Edition.
It is possibly a bug, but I would definatelly advise closing the diagram
prior to making table changes.
As far as bit fields not showing up when creating indexes.
This is not a bug. you cannot create an index on a bit.
Indexes are only valuable when there is a varied amount of data. For a bit
there are only 2 possible values 0 and 1, so in the best case scenario the
chance of a record being selected is 50%. The selectivity of the data of a
column with a bit data type is not great enough to justify the cost of havin
g
to look at the index.
"Cory Harrison" wrote:

> This morning I spent 3 hours drawing a diagram for 75 tables, had it looki
ng
> very nice, was saving on a regular basis, but then I couldn't save data
> structure changes to a table until I closed the diagram out, which also
> wanted to save one last time. So when I opened it back up, nearly all of
my
> work was gone. Tables that I had removed are now back. Objects are not
> arranged like I had them, they're all in a straight line. All relationshi
ps
> except for 4 or 5 are gone. I pretty much wasted all that time. Is this
a
> bug?
> I am having other Enterprise Manager problems too, like I cannot see bit
> fields in my available fields list while creating indexes. I would think
> that I should reinstall Enterprise Manager but I've reinstalled twice with
> the client tools on sql2k and still have the bit field problem.
>
> thank you,
> Cory
>
>|||Thanks for the advice regarding the software tools.
As for the indexes, yes, you can have bits in indexes but only with sql2k.
I understand the reasoning behind not indexing a column based on a bit, but
it would speed things up in a covering situation if you could put your more
selective fields first then put the bit field last because sql server would
not have to do a bookmark lookup, which is all I want it for. I have some
situations where multiple fields are used in a query but a bookmark lookup
is needed because of that one stupid bit field. I can write scripts that
add bits to indexes, I just cannot do it in Enterprise Manager. I have
confirmed this with Kimberly Tripp, and if she says it must be a bug, I
doubt many of us should argue, I just don't know what to do about it. I was
ok with just writing scripts as a workaround until I lost 3 hours worth of
work earlier and thought that it may be related.
thanks,
Cory
"rmartinez" <rmartinez@.discussions.microsoft.com> wrote in message
news:8E0B6BE8-CCB0-478D-A0C1-9256B8753281@.microsoft.com...
>I have not used diagraming in sql server enterprise manager as extensively
>as
> you have. In general it is not a good tool for creating the database
> diagram.
> It is good for a few tables.
> You should consider using a tool that was properly built for diagraming.
> Like ErWin, I even Use Visio Professional Edition.
> It is possibly a bug, but I would definatelly advise closing the diagram
> prior to making table changes.
> As far as bit fields not showing up when creating indexes.
> This is not a bug. you cannot create an index on a bit.
> Indexes are only valuable when there is a varied amount of data. For a bit
> there are only 2 possible values 0 and 1, so in the best case scenario the
> chance of a record being selected is 50%. The selectivity of the data of a
> column with a bit data type is not great enough to justify the cost of
> having
> to look at the index.
> "Cory Harrison" wrote:
>

Database Design Assistance

I have 2 tables that will be created from flat files and recreated on a
daily basis with 300,000+ records and growing daily (tables will be
truncated and re-loaded via a batch process because we do not "own" the
data). I would like to extract the address information from these
tables and create a simple database design that will be relational and
efficient (there are additional Customer and Account fields but these
are the fields currently of interest).
Customer [table name]
TaxID [PK]
Addr1
Addr2
City
State
Zip
Country
MAddr1
MAddr2
MCity
MState
MZip
MCountry
Account [table name]
AccountNumber [PK]
TaxID
SecTaxID
Addr1
Addr2
City
State
Zip
Country
Every Customer in Customer table will have 2 addresses per TaxID,
Mailing and Physical even if they are the same. Every AccountNumber
will have exactly 1 address (Mailing) associated with it. So, if you
are a customer with 2 accounts, you would have 1 record in the customer
table (with both address and Mailing address populated) and 2 records
in the Account table. All 4 addresses could be the same or unique.
Mailings will EITHER go out at the Account level OR the Customer level
so I'm not sure if it is beneficial to combine the addresses into one
table or just create some sort of Account address table and another
customer address table but then i get one-to-one relationships and I'm
not sure how beneficial that is. This is how the data will be received
from the vendor and there isn't any leeway there. Any assistance that
could be provided would be greatly appreciated.Hi
If you do not store all the addresses as received you will probably end up
with a data cleansing excercise to make sure that you are not duplicating th
e
same address that is input slightly differently. Moving the address out to
another table will be a good idea if you have occassions to retrieve the
customer/account information without the address details. I would start with
a normalised design and if performance is being severely compromised you can
then de-normalise.
John
"jasonl22@.yahoo.com" wrote:

> I have 2 tables that will be created from flat files and recreated on a
> daily basis with 300,000+ records and growing daily (tables will be
> truncated and re-loaded via a batch process because we do not "own" the
> data). I would like to extract the address information from these
> tables and create a simple database design that will be relational and
> efficient (there are additional Customer and Account fields but these
> are the fields currently of interest).
> Customer [table name]
> TaxID [PK]
> Addr1
> Addr2
> City
> State
> Zip
> Country
> MAddr1
> MAddr2
> MCity
> MState
> MZip
> MCountry
> Account [table name]
> AccountNumber [PK]
> TaxID
> SecTaxID
> Addr1
> Addr2
> City
> State
> Zip
> Country
> Every Customer in Customer table will have 2 addresses per TaxID,
> Mailing and Physical even if they are the same. Every AccountNumber
> will have exactly 1 address (Mailing) associated with it. So, if you
> are a customer with 2 accounts, you would have 1 record in the customer
> table (with both address and Mailing address populated) and 2 records
> in the Account table. All 4 addresses could be the same or unique.
> Mailings will EITHER go out at the Account level OR the Customer level
> so I'm not sure if it is beneficial to combine the addresses into one
> table or just create some sort of Account address table and another
> customer address table but then i get one-to-one relationships and I'm
> not sure how beneficial that is. This is how the data will be received
> from the vendor and there isn't any leeway there. Any assistance that
> could be provided would be greatly appreciated.
>|||so you import it exactly as it comes from your source. that is life,
and how it is done. .
from there, you have an account table, that ONLY has accountnumber,
taxid, and maybe sectaxid depending on what the heck sectaxid is.
you will have an "address table." it will have primary key of
Taxid,Addresstype
AddressType
TaxID
Addr1
Addr2
City
State
Zip
You will have an addresstype table.
it will have
addresstype (M or P)
AddressDescr (Either "Mailing or Physical)|||Jason,
I'd go for something like
Customer [table name]
TaxID [PK]
Physical_address_id
-- both could point to the same row
mailing_address_id
SInce one and the same addres can be both physical and mailing, I'd
rather not have address_type column in address table|||my answer was more normalized......

Database Design Assistance

I have 2 tables that will be created from flat files and recreated on a
daily basis with 300,000+ records and growing daily (tables will be
truncated and re-loaded via a batch process because we do not "own" the
data). I would like to extract the address information from these
tables and create a simple database design that will be relational and
efficient (there are additional Customer and Account fields but these
are the fields currently of interest).
Customer [table name]
TaxID [PK]
Addr1
Addr2
City
State
Zip
Country
MAddr1
MAddr2
MCity
MState
MZip
MCountry
Account [table name]
AccountNumber [PK]
TaxID
SecTaxID
Addr1
Addr2
City
State
Zip
Country
Every Customer in Customer table will have 2 addresses per TaxID,
Mailing and Physical even if they are the same. Every AccountNumber
will have exactly 1 address (Mailing) associated with it. So, if you
are a customer with 2 accounts, you would have 1 record in the customer
table (with both address and Mailing address populated) and 2 records
in the Account table. All 4 addresses could be the same or unique.
Mailings will EITHER go out at the Account level OR the Customer level
so I'm not sure if it is beneficial to combine the addresses into one
table or just create some sort of Account address table and another
customer address table but then i get one-to-one relationships and I'm
not sure how beneficial that is. This is how the data will be received
from the vendor and there isn't any leeway there. Any assistance that
could be provided would be greatly appreciated.Hi
If you do not store all the addresses as received you will probably end up
with a data cleansing excercise to make sure that you are not duplicating the
same address that is input slightly differently. Moving the address out to
another table will be a good idea if you have occassions to retrieve the
customer/account information without the address details. I would start with
a normalised design and if performance is being severely compromised you can
then de-normalise.
John
"jasonl22@.yahoo.com" wrote:
> I have 2 tables that will be created from flat files and recreated on a
> daily basis with 300,000+ records and growing daily (tables will be
> truncated and re-loaded via a batch process because we do not "own" the
> data). I would like to extract the address information from these
> tables and create a simple database design that will be relational and
> efficient (there are additional Customer and Account fields but these
> are the fields currently of interest).
> Customer [table name]
> TaxID [PK]
> Addr1
> Addr2
> City
> State
> Zip
> Country
> MAddr1
> MAddr2
> MCity
> MState
> MZip
> MCountry
> Account [table name]
> AccountNumber [PK]
> TaxID
> SecTaxID
> Addr1
> Addr2
> City
> State
> Zip
> Country
> Every Customer in Customer table will have 2 addresses per TaxID,
> Mailing and Physical even if they are the same. Every AccountNumber
> will have exactly 1 address (Mailing) associated with it. So, if you
> are a customer with 2 accounts, you would have 1 record in the customer
> table (with both address and Mailing address populated) and 2 records
> in the Account table. All 4 addresses could be the same or unique.
> Mailings will EITHER go out at the Account level OR the Customer level
> so I'm not sure if it is beneficial to combine the addresses into one
> table or just create some sort of Account address table and another
> customer address table but then i get one-to-one relationships and I'm
> not sure how beneficial that is. This is how the data will be received
> from the vendor and there isn't any leeway there. Any assistance that
> could be provided would be greatly appreciated.
>|||so you import it exactly as it comes from your source. that is life,
and how it is done. .
from there, you have an account table, that ONLY has accountnumber,
taxid, and maybe sectaxid depending on what the heck sectaxid is.
you will have an "address table." it will have primary key of
Taxid,Addresstype
AddressType
TaxID
Addr1
Addr2
City
State
Zip
You will have an addresstype table.
it will have
addresstype (M or P)
AddressDescr (Either "Mailing or Physical)|||Jason,
I'd go for something like
Customer [table name]
TaxID [PK]
Physical_address_id
-- both could point to the same row
mailing_address_id
SInce one and the same addres can be both physical and mailing, I'd
rather not have address_type column in address table|||my answer was more normalized......