|
yousefshokati wrote: can i omit the foreign key constraint in this design without loosing the normal form of my database ?
No, it needs a primary key to be considered a normalized database. You could use the steps as described in the wikipedia to validate the model; inconsistencies should disapear when your model hits 3NF.
Can you show us some example-data in those tables? It's easier to validate the model if there's a sample-population.
Bastard Programmer from Hell 
|
|
|
|
|
If you ignore key constraints your database will shortly deteriorate into a pile of garbage. While it is convenient during development and your business layer SHOULD cater for all your relational requirements having a rigid set of relationships in your data structure will INSURE that your database is not a pile of steaming poop!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You must be careful how you create your tables. Make sure you have the right entities identified and then normalise them and create your tables accordingly.In some cases, the foreign key becomes part of the composite key(i.e. a multi-column primary key)
Since I am not familiar with what you have in mind, I will use a different example. Consider a database for students, courses, and registration
Student(StudentId, FirstName,LastName, DOB, SIN....)
Course (CourseID, CourseName)
Semester(SemesterId, StartDate, EndDate)
CourseReg(StudentId,CourseID,SemesterID)
The primary keys are bold and underlined.The primary key for CourseReg is a composite key made up of foreign keys. Foreign keys are not always used as part of the composite key; in general use the fewest possible columns in a composite key while maintaining normalisation(3NF is usually good enough)
|
|
|
|
|
Hello . Thanks for your help .I ask this question because i had a problem in my own design
I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables .
I am going to ask a total question from you
: What should I do to solve the problem with the circles that appear because the foreign key constraints
?
thanks
|
|
|
|
|
I am not sure what you mean by "circles" but I suppose you mean some sort of cyclic constraints.
Before you create tables, you need to list the entities first. Entities are things tangible or intangible for which you want to capture.Also, determine what kind of information each entity should contain. At this point you're not thinking of tables. Once you have determine the entities,
then think of them as tables, and then normalise them to 3NF.
Since I am not an engineer, I can't figure out what the entities were to start with. So, go back to the point before you created those tables, what were the entities or objects whose information you wanted to capture?
|
|
|
|
|
May be to help be better, explain what these tables are for
Properties(PropertyID (PK) , Name , HardwareTypeID)
Values( ValueID (PK) , ValueTitle , PropertyID (FK))
Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK))
|
|
|
|
|
yousefshokati wrote: and with foreign keys that i am sure are needed
Based on what you posted I am sure that some are are not needed.
I seriously doubt that there is any reason for Properites to have HardwareTypeID. That is just an enumeration which can stand on its own. You tie the enumeration to something else via Values and that is sufficient.
|
|
|
|
|
I'm using VS2008 with C# and SQL CF v3.5 to create an inventory application for a mobile computer. I have never worked with SQL Compact before, but I'm pretty sure I've got it right from the database perspective. I've added my DataSource, TableAdapters, queries to fill my ComboBoxes, etc. When I deploy to my WM 6.5 device, I've made sure that the SQL Compact CAB files have been installed, and that the database exists on the device. When I use any of the ComboBoxes in the form, they are all blank and none of them are populated. In VS 2008 when I 'Preview Data' it's all there. Not sure where to go from here to debug?
|
|
|
|
|
Hello all,
I dont know it might be an easy thing but i didnt work it around.
I have two tables and want to join them.
Normally my join statement is as follows:
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = co.CID
but this time the columns in the columns in the tables that has relation is similar but with one difference.
C.ID = '123'
co.CID = 'c123'
so how can i join this;
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = 'c' + co.CID
something like that  is it possible? or i need to accomplish what i am trying in two different statements?
Thank you very much for your help
|
|
|
|
|
Your second join works on some systems. Did you try it on your system?
|
|
|
|
|
Yes I have tried but it is not accepting it.
When i try it I am getting thois error:
Dynamic SQL Error
expression evaluation not supported
Strings cannot be added or subtracted in dialect 3
|
|
|
|
|
I have found the solution. The solution is
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = ('s' || co.CID)
|
|
|
|
|
It seems u are using PL/SQL not T-SQL.
Good day,Good job,Good life
|
|
|
|
|
try this
SELECT c.Name, co.Name
FROM City C
inner JOIN Country co ON c.ID = co.CID
where C.ID = '123'
and co.CID = 'c123'
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Your query will return 0 rows.
|
|
|
|
|
|
Have you really thought this one through? How can c.ID (and co.CID) equal 123 and c123 simultaneously? Have you come across some new quantum extension to T-SQL that allows uncertainty in equality tests?
|
|
|
|
|
Use substring
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)
|
|
|
|
|
Try
'c' + c.ID = co.CID
and not
c.ID = 'c' + co.CID
If C.Id is of integer type then cast it
'c' + Cast(c.ID as varchar(20))= co.CID
Try this
Declare @City table (Id int,Name Varchar(20))
Insert Into @City
Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'
Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
Insert Into @Country
Select 1,'c123','Country 1' Union All
Select 1,'1','Country 1' Union All
Select 1,'c5','Country 1'
SELECT c.Name, co.Name
FROM @City C
JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))= co.CID
Hope this helps
Niladri Biswas
|
|
|
|
|
I have an SQLite database and I need to load the database as in-memory database. Is it any way to do this?
I am using ADO.NET Data Provider library for SQLite to do all the DB Operations.
Please help.
|
|
|
|
|
|
I already read this documentation, but did't find any solution.. 
|
|
|
|
|
|
|
I don't know. If ADO.NET does not provide backup methods, and does not support SQL backup commands, then you will need to call native code that does perform backups for you. That would require some P/Invoke stuff, whether you would isolate that in a wrapper is up to you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
