|
Creating the relationships ensures database integrity.
If there is a relationship between table A and table B, where table A's PK is a FK in table B, then SQL server will not allow you to delete records in table A that has child records in table B.
I would not be surprised if there were not also performance benefits when joining table A and B aswell but I am not sure if this is the case.
|
|
|
|
|
You can get records using joins from any two tables. But if u are not using PKs and FKs how can u assure that a particular row or record in a child table is associated to a particular row in the master or parent table. By using PKs and FKs database forces a rule on data table so invalid data cannot be inserted.
Joins are used to fetch record from more than 1 tables but donot force the rules on datbase tables.
Do good and have good.
|
|
|
|
|
I need some help to find a solution for the problem. I have a VB6 application using ADO that needs to connect to a SQL Server Database with a UserID/Password for the secured database where the UserID/Password for the SQL Server access is different than the NT workstation Login ID and Password. The clients SQL Server is authorized for NT Authenication only and they will not allow SQL Authentication. Can this be done with ODBC? Or is there another method or can it be done at all? The environment is Server 2003, Active Directory, SQL Server 2000 and VB6 using ADO. Need help ASAP. Thanks..
flexTecs, Email Archive and eDiscovery Tool Development
|
|
|
|
|
1-add loging to SQL Server (windows logins from domain users)
2-use trusted connection in connection string:
"Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"
|
|
|
|
|
OK, but does that me that I need to setup a group that I use in the Security Login on SQL Server and then have a user in that group that the Application uses and a user that is the Windows NT login. Remember I need the application to use a login that is the DBowner but the NT workstation login is a user. I would like to expand on this so that I clearly understand. So far the testing that we have done requires that the NT Workstation Login be the same as the SQL Login for the database. Thanks
flexTecs, Email Archive and eDiscovery Tool Development
|
|
|
|
|
For windows authentication to work as I described..Users must login to domain, not using a local machine login (You should setup an active directory).
|
|
|
|
|
You cannot do it at all. When using Windows Authentication, the identity of the thread is used to make the connection. You can only change the identity of a thread (to be different from the identity which the process is running under) using impersonation, but I'd really not like to try doing that with VB6.
|
|
|
|
|
I have a dataBase in Sql Server 2000 , and i want to connect to DataBase
I have crerated an conectionString by add connection in the server expolrer
(C#.Net Invironment) evry thing about connetion is correct but when program trying to connect DataBase an Exception occurs .
Exception Message is : Login failed for user 'Login1'.
Login1 is my login To DataBase registred in Security section of SqlServer 2000
---------------------
Areff Bahrami(KAVEH)
Areff.HB@Gmail.com
---------------------
|
|
|
|
|
Is the server configured for SQL Server authentication, Windows authentication or both (mixed)?
|
|
|
|
|
Hi all,
I've designed a database for a recruitment site, this is the my first time to design such a complex model, I feel that I have gone so far over-normalizing this database and made it too complicated. I need your comments/suggestions on its design. Please bear with me and I'll provide you with any details you might need.
An image of the database design model is here http://wunex.blogspot.com
|
|
|
|
|
Eliminate lookup tables that have only two or fewer choices (e.g. Gender). Military service - this is either yes/no or number of years - not a lookup
A lookup for Grades doesn't make sense - wouldn't this be a continuous range of values either a number (3.0) or a letter (A, B, C)?
Avoid "mapping tables" like Resumes_fields and Resumes_languages. In both cases, what is probaly wanted is
another tabel: Language_skills:(ResumeID,LanuageID,Language_proficiencyID), unless you really intend an n-to-m cross-reference (these can be difficult to maintain)
NoticePeriod does not seem like an attribute of a resume (rather a job-history) and should probably just be a range-limited value(0-n weeks) rather than a lookup.
|
|
|
|
|
Hey Rob, I just don't know what to say man....thank you
About your comments:
Eliminating lookup tables that have only 2 choices is reasonable, the reason why I created them because I don't have a clear idea as how much is too much normalization, I thought that even a table with two values should be separated!....but ok I'll eliminate them.
Grades here are (Excellent, Very Good, Good, Satisfactory), so would you still recommend eliminating it?
I don't understand this
Rob Graham wrote: Avoid "mapping tables" like Resumes_fields and Resumes_languages.
These two tables are junction tables between Resumes and Fields(job/company field) and Resumes and Languages(Spoken languages) respectively.
I don't understand this:
Rob Graham wrote: In both cases, what is probaly wanted is another tabel: Language_skillsResumeID,LanuageID,Language_proficiencyID), unless you really intend an n-to-m cross-reference (these can be difficult to maintain)
what do you mean by in both cases I need another table(Language_Skills)? and what is n-to-m cross-reference?
NoticePeriod contains values(1 week, 2 weeks,....,1 month) so is this ok to keep it or you still recommend to eliminate it and just get the value from user and put it in a varchar field in resumes/job_history?
Rob, thank you again and excuse me for being such a nag, but my resources are limited to forums, e-books, articles, and tutorials.
|
|
|
|
|
iskaza wrote: I thought that even a table with two values should be separated!
If it's always goint to be just a boolean answer (yes/no, true/false, etc.) then a boolean (bit) field will do, you can format the meaning of 1/0 in the UI as appropriate.
iskaza wrote: These two tables are junction tables between Resumes and Fields(job/company field) and Resumes and Languages(Spoken languages) respectively.
Do you really intend an m-to-n relation where both m & n can be > 1? I think you really want a
table related to a resume that contains the lanuage skills of the applicant: rows with resumeid, Language Id, and Language proficiency. The lanuage proficiency is an attribute of the applicant/resume, not of the language itself as your present structure implies.
Is a field an attribute of a resume, or of a job (in which case maybe its just a column in History_jobs)
If it is an attribute of the resume, then perhaps resume_fields isn't meant as a cross-reference, but just n fields per resume. Last modified: 10mins after originally posted -- Oops, hit post before I was done...
|
|
|
|
|
Yes there's a many-to-many relationship between (Resumes and Fields), (Resumes and Languages).
Rob Graham wrote: I think you really want a
table related to a resume that contains the lanuage skills of the applicant: rows with resumeid, Language Id, and Language proficiency. The lanuage proficiency is an attribute of the applicant/resume, not of the language itself as your present structure implies.
The languages table has predefined languages(English, French,....), so how would I map LanguageProficiency to every language skill per applicant. Yes LanguageProficiency is an attribute of the applicant's language skill but I have a FK named LanguageProficiency in the Languages table which further specifies the LanguageProficiency for every language skill per applicant...!
About Field...Well, Field is a table which contains the various business activities (Accounting, Construction, Import & Export, Computer Hardware,......). So, I make use of this table as the Employer's company field and the Applicant's required job field(s), just like the countries table, I use it to specify the Employer's country, the applicant's country and the location of a posted job.
|
|
|
|
|
iskaza wrote: Yes there's a many-to-many relationship between (Resumes and Fields), (Resumes and Languages).
I would argue that there is really a 1-n relationship between a resume and the applicable fields.
There is a different 1-m relation betwen a field and applicable jobs. although that implies a possible n-m relation between resumes and jobs, it will be easiier in the long run to maintain two 1-n relationsips than 1 m-n relation. Write some sample qureies that would use your junction tables (in particular some insert and update queries). See if they really make sense. If they do, then fine. I'm just generally suspect of tables that contain only fk's from non-lookup tables. iskaza wrote: Yes LanguageProficiency is an attribute of the applicant's language skill but I have a FK named LanguageProficiency in the Languages table which further specifies the LanguageProficiency for every language skill per applicant...!
I find it cumbersome to have to think about having to select the combination of lanugage and proficiency together as a single attribute of a resume. I would seem to make more sense to select the languages and proficiencies separately as values in a record related to a resume. But, whatever makes sense to you...again I would walk through some usage scenarios before settling on a design.
Usually it is good to start with a highly normalized design and then de-normalize as needed for ease of (programmer) use and efficiency.
|
|
|
|
|
Rob Graham wrote: I would argue that there is really a 1-n relationship between a resume and the applicable fields.
I'd like to give the applicant the chance to have more than 1 desired job in his resume, that makes a m-m relationship between resumes and fields.
Rob Graham wrote: I find it cumbersome to have to think about having to select the combination of lanugage and proficiency together as a single attribute of a resume
It's not a single attribute, language is an attribute of a given resume, proficiency is an attribute of that language attribute...it's attribute of an attribute, what I want to do is:
specifying proficiency of every language selected by the applicant to layout this part of the whole resume like:
Language Skills:
English Fluent
French Fair
So, do you have any alternative way to do that?
One last silly question: what exactly is meant by a lookup table and what distinguishes it from aother tables?
|
|
|
|
|
iskaza wrote: do you have any alternative way to do that?
Language skills:
<--------pK----------->
ResumeID | LanguageID | Skill Spoken | Skill Written
-----------------------------------------------------
1 | 1 | 2 | 1
1 | 2 | 3 | 4
Languages:
LanuageID | Lanuage Name
------------------------
1 | English
2 | French
Lanuage Skill Level:
LevelID | Level Description
----------------------------
1 | Fluent
2 | Good
3 | Fair
4 | Poor
5 | none
I can change my mind about the skill level(s) without having to delete and re-add the row.
I can extend the information (as I did in the example) more easily.
iskaza wrote: hat exactly is meant by a lookup table and what distinguishes it from aother tables?
A lookup table is basically an enumeration - it provides an integer substitute for one of an enumerated set of values. Its main purpose is to prevent unintended problems caused by user mispelling, etc. and to limit values to a pre-determined set of possibilities. This makes searching and sorting on values of the column that useds the lookup much more deterministic, and improves performance, since integer fields are easier to index.
|
|
|
|
|
1) Your approach suggests that I can do the same to (Resume_Fields) hence eliminating all of the junction tables approach....is that right? if it's so, why I should try to avoid junction tables?
2) About lookup tables; if it's just a matter of avoiding misspelling errors, can't I just populate a comboBox or whatever with the hardcoded values and let the user select from them then extract this selected value and insert it right in a field of the table without the need to reference any lookup tables that would contain these predetermined values? I mean is there a clear-cut rule as when I should to use lookup tables and when I should not?
|
|
|
|
|
iskaza wrote:
1)
Yes.
2) It's better to do this with a database table - you can change the wording of the string values (for instance translate to another language) or extend the enumeration without recoding/recompiling - and it makes the database more self-documenting. No clear cut rule, but I prefer to keep everything thats about the data in the database. It's also a storage space saving device - the integer id takes less space than the equivalent string in the tables where it is used.
|
|
|
|
|
iskaza wrote: 2) About lookup tables; if it's just a matter of avoiding misspelling errors ...
The golden rule for all databases: Keep all instances of data in one place, and one place only. Refer to it if necessesary. The guy who ends up with the maintenance will thank you. 
--
-= Proudly Made on Earth =-
|
|
|
|
|
Hi i am using access in my webapplication.I am facing a lot of problems like connection already open.My question is why MS access is not suitable for web application ?
Thanks and regards
|
|
|
|
|
sishya wrote: My question is why MS access is not suitable for web application ?
Because it is fundamentally a single user file based database, not a multiuser server.
|
|
|
|
|
Do you have the 'Default Open Mode' set to 'shared' in the Database Options?
Steve
|
|
|
|
|
Hi Rob and all,
I have a recruitment site which is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's. and there's a couple of other databases. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask him again to sign in to search jobs or modify his resume. Now, with that said I was wondering can I create a FK say UserID in the resumes table in the (Recruitment) database whose value is the PK of the Users table of the (portal) database. So, is that possible?? I mean referencing a PK from a table in another database?? If not, what is the best approach to integrate multiple databases within that portal and to implement the above functionality.
Thanks
|
|
|
|
|
JobSeeker is in the MyTestDB database and Resumes is in the MyTestDB2 database.
select * from MyTestDB..JobSeeker js inner join MyTestDB2..Resumes re on js.JobSeekerID = re.JobSeekerID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
