Database design question

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.Christian H (no@.ni.na) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:

Quote:

Originally Posted by

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.

Database design for versioned data

We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.

Database design for versioned data

We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.

Database design for versioned data

We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-pr...orall
y/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.

Database design

I have got a design issue.I have got 4 tables,having relationships.Now,user will edit records and save them,but I want to save the values which were exixsting before they were edited and saved.Implying,if a record is edited 50 times,then the values of each edit needs to be saved.How best ,can I achieve this.I can see 2 answers, there may be more.
1. Fire a trigger on the before update that would collect the data and populate it some where else.
2. Use Stored procedure to do all updating and inserting then you can add code to each procedure that would grap the values before it updates.

I would go with the SP Method as triggers can get busy.|||Indeed ... create a history table where each time an update happens you will save a record|||Here is a nice Generic audit trail trigger code:

http://www.nigelrivett.net/AuditTrailTrigger.html