Re: Help on design question
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 11/17/04
- Next message: Wayne Snyder: "Re: how to diff the data of same table of two sql servers"
- Previous message: RootChaos: "SuperSocker Bind Failed on port TCP:1433 Error"
- In reply to: Marek: "Help on design question"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Nov 2004 08:05:43 -0500
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
- Next message: Wayne Snyder: "Re: how to diff the data of same table of two sql servers"
- Previous message: RootChaos: "SuperSocker Bind Failed on port TCP:1433 Error"
- In reply to: Marek: "Help on design question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
