Database design question

[Christian H]

Hello,

We have a problem we're not quite how to solve best. We're working on a
Web-application where some values that are used, are pre-defined (default
values), and other values should be user-defined (users can add additional
values)
Currently, the 2 different things have been separated into 2 different
tables. The problem we're having now, is when the values from the 2 tables,
should be referenced in another table, i.e when the items are saved as a
part of a form-submission. Should we use 2 different columns to represent
the ID for the 2 different tables?

Here is an exmple of that design:

DropDownValues_ SystemDefined
---------------------------------------
ID
Name
Value

DropDownValues_ CustomerDefined
----------------------------------------
ID
CustomerID
Name
Value

DropDownValues_ SavedItems
----------------------------------
ID
DropDownID_Syst em
DropDownID_Cust omer



The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.

DropDownValues_ CustomerAndSyst emDefined
----------------------------------------
ID
CustomerID
Name
Value


Regards Christian H.

[Erland Sommarskog]

Re: Database design question

Christian H (no@ni.na) writes:

The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.
>
DropDownValues_ CustomerAndSyst emDefined
----------------------------------------
ID
CustomerID
Name
Value

Your description is very abstract, and I might change my mind if knew
more about what's in this table. But from the description you have
given, this latter solution is what I would prefer.

The presumption here is that the values really describe the same
entity.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jason Kester]

Re: Database design question

Assuming these are actually the same thing, and that you simply want to
track that some are Customer defined, and that you'll want to enforce
relationships to either system or customer records, here's what I'd do:

DropDownValues
---------------------------------------
DropDownValuesI D int identity
Name
Value


DropDownValues_ SystemDefined
---------------------------------------
DropDownValuesI D int


DropDownValues_ CustomerDefined
----------------------------------------
DropDownValuesI D int
CustomerID


DropDownValues would serve as the base of your two classes, and would
be used to generate keys and hold common information. Your customer
data could extend that table, and the system ones would simply need a
placeholder record to identify themselves.

Of course, if you won't need to enforce relationships to System or
Customer records (for instance, another record that can only hook up to
a System value, but not a Customer one), then you could simply add a
nullable CustomerID to the DropDownValues table and be done with it.

Good luck!

Jason Kester
Expat Software Consulting Services

http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!

http://www.blogabond.com/

Christian H wrote:

Hello,
>
We have a problem we're not quite how to solve best. We're working on a
Web-application where some values that are used, are pre-defined (default
values), and other values should be user-defined (users can add additional
values)
Currently, the 2 different things have been separated into 2 different
tables. The problem we're having now, is when the values from the 2 tables,
should be referenced in another table, i.e when the items are saved as a
part of a form-submission. Should we use 2 different columns to represent
the ID for the 2 different tables?
>
Here is an exmple of that design:
>
DropDownValues_ SystemDefined
---------------------------------------
ID
Name
Value
>
DropDownValues_ CustomerDefined
----------------------------------------
ID
CustomerID
Name
Value
>
DropDownValues_ SavedItems
----------------------------------
ID
DropDownID_Syst em
DropDownID_Cust omer
>
>
>
The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.
>
DropDownValues_ CustomerAndSyst emDefined
----------------------------------------
ID
CustomerID
Name
Value
>
>
Regards Christian H.