Quote:
|
Originally Posted by hrdwareguy
I am going to propose a solution that uses an SQL server but I have some questions for the SQL GURU's around here.
|
A very good proposal, I would say (can't claim to be a guru of any kind though). If you are looking for a free database server, might I recommed
PosgtreSQL - it's full featured, mature, performat and a joy to work with
Quote:
|
First off, can sql tables have associated indexes and if so, how do you access the information via the index?
|
Tables not only
can have indices, but it's an odd one that doesn't. When you perform a query, the optimizer (and these are vastly different in their design and performance between different database products) will determine which, if any, of the indices available for the tables involved in the query are likely to improve the query performance, and will create an appropriate execute plan. Some databases allow you to override the optimizer and specify which indices to use, but this is
very rarely necessary.
Quote:
|
For example if I enter a phone number I want to be able to jump to that phone number if it exists. If it does not exist, I need to go to the closes match and be able to go to the previous and next records.
|
The first part is trivial, you create a table (let's say 'employees') and create an index on the 'phone' field, when you execute "SELECT * FROM employees WHERE phone = '123-456-7890" the index will be used to fetch the record instantly, if it exists.
The second part is somewhat more complicated because I am not sure what you mean by 'closest match'. Is 'closest' the nearest one if the phone number is treated as a single integer, or one with the least number of substitutions to arrive to the one you want? Or something else entirely?
Quote:
|
Next, I need to know how to set permissions on tables. I have one table that everyone needs read access to, but only 3 people need write access to. I will have both internal and external sources looking at some of these tables so will need to set the security appropriately.
|
This is also something that differs somewhat between database engines, but usually it's some variant of the GRANT/REVOKE commands. Usually along the lines of "GRANT[list of privileges] ON [tables/schemas/views/indices or pretty much any other database object] TO [users/group]" - simple, no? Consult the documentation for the database engine you choose to work with for the specifics. The most basic privileges will include SELECT, INSERT, UPDATE, DELETE (and usually many more advanced ones) so they are more than granular enough to do what you want.
Of course depending on your actual application, you may want to always connect as the same user and handle permissions in your app (for performance reasons, this is usually the way it's done in web applications).
Quote:
|
I will be doing my developnet in Delphi, not that it should make a difference though.
|
Can't claim any experience there.
Quote:
|
Thanks for your advice, I will probably have more questions later.
|
Good luck. Will be glad to answer any further questions.