Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key being
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key being
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, this
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItems.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between the
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can come
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
--
Many thanks in advance for any assistance
A confused MarekI would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
--
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftly
change my design.
Marek
"Mike Hodgson" wrote:
> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expanded
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinate
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> > Hi,
> >
> > I have a fundamental design issue that I would appreciate any assistance
> > on.
> > I have a DB that is aimed at tracking jobs that come into a department and
> > the charges associated with these jobs.
> >
> > Most jobs are handled by that dept, but some need to be outsourced to
> > external suppliers. All the products and services that are provided by
> > that
> > dept are stored in a table called tblInternalItems with the primary key
> > being
> > a field called ItemID. If a job requires the use of an external supplier,
> > I
> > store that info in a table called tblExternalItems with the primary key
> > being
> > a field called ItemID. Because this info needs to be accounted for and
> > accounts notified at the end of each month on what we owe the suppliers,
> > this
> > seems to make sense and the final figures easy to calculate.
> >
> > To keep a track of what customers have had, I have a table called
> > tblJobItems.
> >
> > Now, the big problem comes in with the fact that a customer can have an
> > item
> > that is provided by my dept, or an item provided by an external supplier.
> > So, I have a field here called ItemID, that being the foreign key between
> > the
> > 2 tables. Now that's the big question - I don't think I can enforce good
> > referential rules in this type of situation where the required value can
> > come
> > from 2 different tables.
> >
> > There are 2 solutions I can think of. Introduce another field in the
> > tblJobItems table to track external supplier items, or merge the two
> > tables
> > into one.
> >
> >
> > I am leaning towards a single table with just an additional field called
> > SupplierID to track any jobs that have been outsourced.
> >
> > That would then make referential integrity easily enforcable.
> >
> > That's what i suspect but would appreciate input to confirm my thoughts
> > before I go ahead and change things.
> >
> >
> >
> > --
> > Many thanks in advance for any assistance
> > A confused Marek
>
>|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek