Database Design Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCAD
Joining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Design Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table
.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to thi
s
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCADJoining across databases is no problem (across instances can be, performance
wise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers.
There's no best way,
though. Consider advantages and disadvantages for both approaches and use th
e one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share
a
> basic part number/part description table, as well as a common customer tab
le.
> What is the best way to design this? Should I have a central database tha
t
> contains the master tables and then have all of the other tables link to t
his
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup so
me
> type of replication)?
> I just was not sure if it was good practice to be constantly joining table
s
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Design Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCADJoining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
Shawki
Hi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
ShawkiHi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
ShawkiHi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki