|
Hi,
In my opinion, your approach with only single "metadata" is better that seven different ones. What about the foreign keys? From your error message I understand that Status is a "metadata" table and docs is an actual data table.
How does the Docs refers to Status? If the constraint is set to ON DELETE CASCADE, do you really want that when a status is deleted, docs which have the corresponding status are also deleted?
If the problem is with update (ON UPDATE CASCADE), I think you shouldn't use keys that are updatable, but instead use surrogate keys.
Also does the main table have seven foreign key columns, one for each choice user makes. This error also occurs if you use the same column for two different foreign keys.
Mika
|
|
|
|
|
Mike,
Sorry, I forgot to clarify the error messsage. Yes the "docs" table is my main table and the "status" is one type in my metadata table. Hence the FK_docs_status relationship name. Some examples of other metadata types/relationships are "dept" and the relationship is FK_docs_dept, or "classcode" relationship FK_docs_classcode.
I have a Name field that represents the choices for each metdata type. For example a status type may have three choices, "Pending", "Complete", and "Closed." Dept may have "Accouting", "Shipping", etc. You get the idea.
I am not allowing the user to modify the uique ID in the metadata table. However, when the user deletes one of the metadata "status" items or one of the "dept" items, I want to "Set Null" the status column or dept column respectively, in my docs table.
Does that clarify it?
|
|
|
|
|
Yes, that seems clear. This is a bit problematic situation in SQL Server, since it does not allow multiple modifying references between same tables. Only NO ACTION is allowed several times. For example:
CREATE TABLE Choice (
[Choice] int not null,
[Type] int,
[Name] varchar(50));
ALTER TABLE CHOICE ADD CONSTRAINT pk_Choice PRIMARY KEY ([Choice]);
CREATE TABLE Doc (
[Doc] int not null,
[Status] int,
[Dept] int);
ALTER TABLE Doc ADD CONSTRAINT pk_Doc PRIMARY KEY ([Doc]);
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Status FOREIGN KEY ([Status]) REFERENCES Choice ([Choice]) ON DELETE SET NULL;
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Dept FOREIGN KEY ([Dept]) REFERENCES Choice ([Choice]) ON DELETE SET NULL; -- This fill fail because of the limitation
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Dept FOREIGN KEY ([Dept]) REFERENCES Choice ([Choice]) ON DELETE NO ACTION; -- However since this is restricting, it will work
What I would suggest is that you use triggers to enforce this kind of referential integrity. Of course you can use declarative constraints on some of the columns, but not on all of them.
Hope this helps,
Mika
|
|
|
|
|
Mike,
Thanks for your suggestions. But I think I'm going to go back to seven separate tables. I can't see any advantage to keeping all the different metadata types in one table if I'm going to have to do extra steps like use triggers to enforce referential integrity. Why do all that if its built right into the designer.
Also, on several other forums others are telling me to go with seven tables in order to use the actions of "Set Null" on delete or Cascade updates, etc.
Right now I can't remember the exact reason I decided to go with one table instead of seven. But if I run into an issue with that design, I will post another question here.
Elgin
|
|
|
|
|
You're welcome. I believe that there's no right answer for this kind of question so you are most likely doing the right choice. Hope that the project goes smoothly!
Mika
|
|
|
|
|
I would recommend the 7 seperate tables approach. It is easier to manage your relationships. Reports and queries are simpler.
While I have never used the meta data approach I have seen a number of discussions here on the subject. Some get quite heated but I beleive the consensus is to retain the tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a table contain column with datatype datatime when i want to save date in it in Hijri format but i dont know how can i do that .
also i Change the regionl and seting from the windows to arabic date
when i use getdate() function its save the date by the gregorian format
plz help me
Kareem Elhosseny
|
|
|
|
|
|
FYI: Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
hi all
can i make TSQL statement like this
select * from test where date between like '%8/08/1429%' and like '%12/08/1429%'
i mean i want to use where clause with between and like conditions
& when i make the query like above its give me this error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'like'.
can i make it or not
plz help me
Kareem Elhosseny
|
|
|
|
|
No. And I hope you're not storing dates as strings.
|
|
|
|
|
no i'm trieng to store date as string but plz tell me which truple i'll meet when i use date as string
Kareem Elhosseny
|
|
|
|
|
Hi,
I suppose that the date-column is defined as datetime so you must first use CONVERT-function to convert the text to datetime. For example:
SELECT *
FROM test
WHERE date BETWEEN CONVERT(datetime, '8/08/1429', 131) AND CONVERT(datetime, '12/08/1429', 131)
Important! the conversion was made using Hijri calendar, based on your other other posts.
Hope this helps,
Mika
|
|
|
|
|
thanks for your help
Kareem Elhosseny
|
|
|
|
|
You're welcome
Mika
|
|
|
|
|
Hi kareem,
Why do y0u need the like? Why not just :
Select * from test where date between '%8/08/1429%' and '%12/08/1429%'
Or possibly try:
Select * from test where date between (like '%8/08/1429%') and (like %12/08/1429%')
Help that helps or I have misunderstood the problem and there is a far deeper reason for your query than is obvious from the data you have given.
|
|
|
|
|
thanks peter for your help really i appeciate your answer and it'll help me
here is m email to contact with me with my pleasure to add me in your contacts kareem_elhosseny@hotmail.com
Kareem Elhosseny
|
|
|
|
|
Hi,
iam using sql server 2000 and now wants to move sql server 2005 where i have to start?
whcih version of sql server i can use like (SP2) or other . where to download ?
please give me suggestion because iam new in this.
Any buddy tell me name of sql server quaries book ?
Regards
Rameez
|
|
|
|
|
rameez Raja wrote: iam using sql server 2000 and now wants to move sql server 2005 where i have to start?
Be more specific? Are you trying to take a SQL 2000 database and move it to SQL 2005? Or, are you wanting to learn how to use SQL 2005 after using SQL 2000?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
hi,i am a bit new to SQL Server 2000.
Using Enterprise Manager,
In my Database Tables,i need to set Multivalued Attributes to my Coloumns.
like
BusNo Source Destination
73 abc xyz
pqr stu
edf ghi
74 123 456
789 112
a12 b34
-------------------------
i need to set "Source" and "Destination" as "MultiValued"
So,please can any one help me out in it.
thanx
|
|
|
|
|
Hi,
I think this is purely modeling question. You should store busses in one table and their routes in another. Optionally you can define possible sources and destinations in another table.
For example:
Table Bus
- busno int
Table BusStop
- BusStopKey int
- BusStopText varchar(50)
Table BusRoute
- busno int (reference to bus)
- SourceBusStopKey int (reference to BusStop)
- DestinationBusStopKey int (reference to BusStop)
Hope this helps,
Mika
|
|
|
|
|
hey Mika.
dint getwat u wantd to explain,let me elaborate a bit more.
eg.
BusNo
73
36
457
----------
Stop73
73
abc
xyz
wer
----------
Stop36
36
qwe
abc
wer
---------
Stop457
457
abc
ghi
klm
In here when we select Source='abc' and Destinatio='wer'
i want BusNo 73 and 36 to be returned.hope u got what i intend to tell??
kindly explain in a bit detail,m really new to Databas Field.
Thanx..
|
|
|
|
|
Hi,
I understood that you are designing tables and wondering how they should be modelled but is that data already stored in table? If so, is the problem in creating a select statement?
Mika
|
|
|
|
|
ya mika,only in select command.
a very small problem as such,but a big one for me.
pl help...
|
|
|
|
|
Ok, sorry about the misunderstanding.
I'm still having some trouble in understanding how the data is stored. Are they stored in two tables as I would imagine? Could you post the table names and their column names so we can go through creating a select on them.
Mika
|
|
|
|