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_System
DropDownID_Customer

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_CustomerAndSystemDefined
----------------------------------------
ID
CustomerID
Name
Value
Regards Christian H.

[Erland Sommarskog]

Christian H ([e-mail]) 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_CustomerAndSystemDefined
----------------------------------------
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jason Kester]

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
---------------------------------------
DropDownValuesID int identity
Name
Value
DropDownValues_SystemDefined
---------------------------------------
DropDownValuesID int
DropDownValues_CustomerDefined
----------------------------------------
DropDownValuesID 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_System
DropDownID_Customer

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_CustomerAndSystemDefined
----------------------------------------
ID
CustomerID
Name
Value
Regards Christian H.