Code table maintenance

Hello,
I am working on the logical model for a database. I need to use a number of
code tables (tables that keep typically name value pairs. I need maintain da
ta like products, services etc).
I am wondering if I increase the abstraction and use one table to represent
the name value pairs but use a category to identify each type is there is an
y value in doing this?
The advantage with this I think is consolidating the data and probably minim
izing the administration
The disadvantages may be too many joins that need to be qualified by the cat
egory type. Also, I may end up having too many self-joins.
Any suggestions'?doesnt seem to make sense to me.
I would keep them seperate.
Greg Jackson
PDX, Oregon

Code Table Maintenance

Hello,

I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?

The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.

Any suggestions???Personally I like this approach because then I don't have a bunch of hash tables scattered around the database. Adding new groups of name value pairs becomes a lot easier.

I haven't found the need to perform self-joins, but yes the large number of joins to the same table tends to be a pain. But, you'd still have to have the joins regardless (just to different tables).

Honestly, I am not sure of the performance benefits. But I think the "compactness" of the solution has value.

My 2 cents, maybe only 1 cent.
Terri

Clustered SQL Servers and chkdsk?

Hi all,
We have 2 SQL Server 2000's in a cluster at work, we started running some
regular server maintenance a few ws back including the basics like defrag
etc...one of the things I was also keen to do was to run chkdsk /F after we
had done the defrags and reboot the servers, I assumed we'd be able to do
this on the passive server, then when it was back up, fail over the active
server and then run it on that one also. Of course, what I failed to
consider was the fact that the shared E drive is a resource which only
'exists' on the active server, so when I fail the active server over, the E
drive goes with it.
From memory the E drive is raid'ed and a colleague mentioned that because of
this there might not be any benefit running a chkdsk anyway, I was hoping to
confirm this, or find a way of chkdsk'ing the shared resource - the only
option I can see at present is to dismount the drive and run it on the
active server but this would then obviously involve some down time, plus
I've never dismounted a drive, and certainly not one being used excessively
like ours on the SQL Server, as a result I have no idea of the consequences.
Any advice would be appreciated - I was previously of the understanding that
running defrag on drives that have large quantities of I/O requests was a
good thing to help reduce the movement of the head in the disc, thus
inproving performance (if only slightly). I assumed chkdsk would be another
worthy program to run (as we do on the other servers, web and the like).
Regards
RobWhen you run chkdsk you're reordering the data on the physical disk. This
would help for most file types. However, I don't think you would gain much
if the drive is striped. On also, on db creation, the file is preallocated
(i.e. taken up whatever the specified size). If the disk is fragmented at
that time, the file is already fragmented. You might gain some perf if you
take the database offline and do chkdsk.
Yup, without a true shared clustered filesystem (i.e. with MSCS/Veritas/etc)
the shared disk is under control of the active node.
Below is a very good whitepaper on fragmentation and what you can do in
regards to sqlserver. I highly recommend a good read.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
-oj
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:OzCId.16421$GG1.8815@.text.news.blueyonder.co.uk...
> Hi all,
> We have 2 SQL Server 2000's in a cluster at work, we started running some
> regular server maintenance a few ws back including the basics like
> defrag
> etc...one of the things I was also keen to do was to run chkdsk /F after
> we
> had done the defrags and reboot the servers, I assumed we'd be able to do
> this on the passive server, then when it was back up, fail over the active
> server and then run it on that one also. Of course, what I failed to
> consider was the fact that the shared E drive is a resource which only
> 'exists' on the active server, so when I fail the active server over, the
> E
> drive goes with it.
> From memory the E drive is raid'ed and a colleague mentioned that because
> of
> this there might not be any benefit running a chkdsk anyway, I was hoping
> to
> confirm this, or find a way of chkdsk'ing the shared resource - the only
> option I can see at present is to dismount the drive and run it on the
> active server but this would then obviously involve some down time, plus
> I've never dismounted a drive, and certainly not one being used
> excessively
> like ours on the SQL Server, as a result I have no idea of the
> consequences.
> Any advice would be appreciated - I was previously of the understanding
> that
> running defrag on drives that have large quantities of I/O requests was a
> good thing to help reduce the movement of the head in the disc, thus
> inproving performance (if only slightly). I assumed chkdsk would be
> another
> worthy program to run (as we do on the other servers, web and the like).
> Regards
> Rob
>|||"oj" wrote ...

> When you run chkdsk you're reordering the data on the physical disk. This
> would help for most file types. However, I don't think you would gain much
> if the drive is striped.
Hi Oj,
I believe our RAID'd discs are raid5 if that makes a difference? (ie, not
striped)

> Yup, without a true shared clustered filesystem (i.e. with
MSCS/Veritas/etc)
> the shared disk is under control of the active node.
ta for the confirmation.

> Below is a very good whitepaper on fragmentation and what you can do in
> regards to sqlserver. I highly recommend a good read.
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Cheers, will take a read-ee now :o)
Regards
Rob