|
You are not describing and Export/Import functionality as this implies you are exporting from your application for use elswhere. This requires an open format usually CSV for large volumes or XML if the volume is not large.
Your requirement of transferring data between 2 versions of YOUR app is a different animal altogether. Use the tool your are most familiar with. Also if it is a sQLLite db it is less open to the user, therefore safer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry, I dind't point out that the export/import feature will serve only to transfer data between two copies of my app, so the SQLite format will be probably the best bet.
|
|
|
|
|
Actually you did point out the requirement, I was just being pedantic. SQLLite is by far the best solution for this requirement!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am starting a C# Winforms application where part of the main data entry form consists of seven comboboxes allowing the user to select a single item from each combobox, or leave the column blank if they so choose. I am representing the choices in the comboboxes as text, of course, but in my main table I will store the unique ID (int) that they choose.
I want the user to be able to maintain the lists that populate the seven comboboxes. What is the best database design for doing this? I have tried two different methodologies so far. First I created seven tables, one for each combobox, since each one has a different meta data type. The tables had three columns - ID, Name, and Description. The ID was the primary key that I related to the main table.
Then I thought it would be better to combine all seven lists into one "meta data" table by simply adding a TYPEID column to the table. That made designing the form to maintain the meta data lists a bit easier. However, when I try to relate my "metadata" table to my main table ON MULTIPLE COLUMNS AND enforce referential integrity I get the following error in the SQL Designer and the VS Dataset Designer:
" Introducing FOREIGN KEY constraint 'FK_docs_status' on table 'docs' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. "
What is the best design pattern? Should I maintain seven different "metadata" tables?
Thank you.
Elgin Rogers
modified on Sunday, August 10, 2008 9:15 AM
|
|
|
|
|
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 ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
