|
Dictionary Table; Records in only 2 lines (for example, a table of sex: male, female) or as much as 40-50 lines and changing of future data in the table Insert / Update / Delete operations possibility of very low.
Combining these tables in a single table, Does it make sense?
For Example;
TBL_Sex
1 Male
2 Female
TBL_WorkType
1 Full-Time
2 Part-Time
TBL_BloodType
1 A Rh(+)
2 B Rh(+)
3 AB Rh(+)
4 0 Rh(-)
5 A Rh(-)
6 B Rh(-)
7 AB Rh(-)
8 0 Rh(-)
Instead of following table;
TBL_DICTIONARY
ID GROUP VALUE
-- ----- -----
1 Sex Erkek
2 Sex Kadın
3 WorkType Yarım Gün
4 WorkType Tam Gün
5 BloodType A Rh(+)
6 BloodType B Rh(+)
7 BloodType AB Rh(+)
8 BloodType 0 Rh(-)
9 BloodType A Rh(-)
10 BloodType B Rh(-)
11 BloodType AB Rh(-)
12 BloodType 0 Rh(-)
|
|
|
|
|
I'd use separate tables for such "enums".
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
So;
Do you use TBL_Sex, TBL_WorkType, TBL_BloodType
or use TBL_DICTIONARY ?
|
|
|
|
|
dataminers wrote: use TBL_Sex, TBL_WorkType, TBL_BloodType
 I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Using separate tables also allows you to create foreign key references so that you can guarantee that the data in your tables remain consistent.
For example:
Tbl_Patient
ID:
Sex:
Work_type:
Blood_type:
additional fields, blah, blah ...
On this new table,Tbl_Patient, you would create foreign key references to tbl_sex, tbl_worktype, tbl_bloodtype, so that only valid values can be stored in those columns.
Good luck on your project and keep asking questions ... it's the only way to learn. 
|
|
|
|
|
Okay, here is what I have done. I went to sql manager and i made my database and all is fine.
I then wanna of course make a program to interface it. So I add linq to sql to my winform
then I take and add the dataconnection. I take and hit sql and i go and find the mdf file that is in the sql server data directory. That seems all fine and dandy.
Then visual studios tells me that it wants to make the files local so I hit yes( should i hit no, cause i wanna use the one that is on the server not local). Anyway I hit yes.
So then I have my tables and I can drag it to the dbml
that seems great. But the problem is when i go to load the dang program it constantly tells me login failed.
so i take and go back to sql manager and the dang database is no longer attached ?
why?
It is making me go in a circle.
I tried to add a connection string for the remote under settings it adds it but i can't select it on dbml for some reason it simply doesn't populate the connection dropdown box.
so i have been trying to do using (var db = mylittledatabase(properties.settings.default.remote)
and that is when i get the whole login issue.
Please help this is driving me nuts.
|
|
|
|
|
tonyonlinux wrote: Then visual studios tells me that it wants to make the files local so I hit yes( should i hit no, cause i wanna use the one that is on the server not local). Anyway I hit yes.
Try and edit your connection string to change the "UserInstance=True" to "UserInstance=False" and see what happens. You maybe copying the db to the output directory every time you run debug and the changes you make are not persisted in the db on the server. Just a guesss. I have been through all these issues many years ago. Just wish I had written everything down.I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
CREATE PROCEDURE usp_AddBook
--Variables
@Title varchar(200),
@Price decimal(18,2),
@Keywords varchar(200),
@Retired bit
AS
BEGIN
SET NOCOUNT ON
DECLARE @bookId int -- SET A VARIABLE TO HOLD THE BOOKID SQL MAKES
-- INSERT THE BOOK INFORMATION
SELECT @bookId = Book.ID from Book where Book.Title = @Title
IF @bookId = null
BEGIN
INSERT Book(Title,Price,Keywords,Retired)
VALUES
(@Title,@Price,@Keywords,@Retired)
SELECT @bookId = @@IDENTITY; -- SET THE BOOK ID TO THE ID SQL MADE
END
INSERT INTO BookNumber (BookID)
VALUES
(@bookId)
END
From my view It should check to see if the @bookID is null and if it is then do the insert
if its not null then the book already exist and i simply wanna insert the bookID into the booknumber table.
The sql manager keeps telling me I can't pass a null to booknumber.bookID (well yeah its non nullable column, but it shouldn't be getting a null value HELP PLEASE
THANKS!
|
|
|
|
|
Shouldn't that be
IF @bookId IS NULL ?I are Troll 
|
|
|
|
|
ha thanks. let me give that a go. i appreciate it! <duh>
|
|
|
|
|
We have been tasked with listing the permissions users have (either by name or by group) on a SQLServer 2005 node. Can anyone provide a script or link to a script to help with this process?
I am cobbling one together from other pieces I've found, but if anyone has a finished product, it would be much appreciated.
Thanks in advance,
Tim
|
|
|
|
|
| We have one that reads all the permissions for a database, deletes them and then restores them, used when restoring production over dev/uat databases. It would probably be another cobble but somewhere to start from (Monday at the earliest) Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I m using SQL 2008 express Edition and Management studio after I attach a database to it, when I go to that folder and try to delete that database , it simply get deleted which is not normal because in enterprise edition we have to first detach then then delete...
Can any one tell me that what is the issue, is this due to Express edition or Somethings wrong in installation or others..
THANKS,
|
|
|
|
|
Hi,
I need to use the
sys.sp_cdc_enable_table procedure with the
@schema, @source_name, @source_name
parameters in a
cursor but I can't find an example to follow. Can someone guide me with this with an example please? Thanks for your help.
Regards
|
|
|
|
|
Can you give a better description of what you are trying to do?
Use some pseudo-code if necessary.
Also, you listed your inputs as @schema, @source_name, @source_name (@source_name twice).
Tim
|
|
|
|
|
Hi, I have a list of table that I need to make them available for auditing by using that particular procedure. I had in mind to call that procedure in a cursor which identifies the table names, builds the script for that procedure by passing the table name and the source to the procedure and executes it. This procedure will be repeated for all the tables. This will avoid hard coding the procedure for all the tables (which I have over 150).
Yes yu're right they should be as listed below;
DECLARE Alter_tables_cursor CURSOR FAST_FORWARD
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
EXECUTE 'sys.sp_cdc_enable_table
@source_schema = N''dbo'',
@source_name = N''TBL_ACTIVITY_ACT'',
@role_name = NULL'
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
END
DEALLOCATE Alter_tables_cursor
Thanks for your reply.
|
|
|
|
|
This is my store procedure but where i am doing wrong
ALTER PROCEDURE [dbo].[Engagement_Get_StandardYears]
(
@clientName NVARCHAR(255)=NULL
)
AS
BEGIN
SET NOCOUNT ON;
SELECT Distinct [Year]
FROM Engagement_Standards
WHERE [Year] IS NOT NULL AND [Client Name] like
CASE
WHEN @clientName IS NULL THEN %
ELSE @clientName
END
ORDER BY [Year];
END
|
|
|
|
|
@clientName NVARCHAR(255)=NULL
Unless you are using unicode characters change all your nvarchar to varchar (non critical but I would do this early)
WHERE [Year] IS NOT NULL
I prefer yo use
WHERE Inull([Year],0) > 0
Also note you are using a reserved word in as a column name - not recommended. You should NEVER need square brackets
Your killer though is this one
WHEN @clientName IS NULL THEN %
ELSE @clientName
change the % to ''
[edit]No wait that is in the where clause
I'm scratching my head trying to understand what you are trying to achieve! Ah got it dynamic where clause, here is one I use all the time
Where (IsNull(@clientName , '') = '' OR clientName = @clientName )
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I use some thing like this Mycroft Holmes.
USE [INVENTORY_ MANAGEMENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE (@ClientName IS NULL OR [Client Name]=@ClientName)
And
(@Year IS NULL OR [Year]=@Year)
And
(@Application IS NULL OR [Application]=@Application)
And
(@Database IS NULL OR [Database]=@Database)
And
(@OS IS NULL OR OS=@OS)
And
(@QuickName IS NULL OR [Quick Name]=@QuickName);
END
|
|
|
|
|
Eeuww - how horrible, working with your database would drive me nuts, who decided to use descriptors as column names. However your where construction is fine.
I have found that you hit a limit on the number of where filters like this you can use, after about 6 or 7 performance can be affected, I have no idea why is I have only seen it a couple of times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hello guys...im new to database administration. What does it mean when we say that we are running two instances of SQL Sever on my computer?? Does this mean, installng sql server two times with different serial numbers??
|
|
|
|
|
|
|
Microsoft® SQL Server™ 2000 supports multiple instances of the SQL Server database engine running concurrently on the same computer. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Applications can connect to each SQL Server database engine instance on a computer in much the same way they connect to SQL Server database engines running on different computers.
For more information about this visit http://msdn.microsoft.com/en-us/library/aa174516%28SQL.80%29.aspx[
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
