need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
T
Not sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremysql

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.
Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>
|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,
|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A
|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...
|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
> >I have a database and trying to put couple of the tables into a ndf
> > file instead of mdf file, any ideas how can I achieve it?
> > Thanks a lot.
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > Is there a way in the enterprise manager I can manually specify tables
> > into secondary ndf filegroups?
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
--
Aaron Bertrand
SQL Server MVP

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE IND
EX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP