SQL Help - Tilted Forum Project Discussion Community

hrdwareguy · 12-21-2004, 10:32 AM

I have used SQL before in some very basic and limited fashion. I know how to write select and insert statements but that's about it.

We currently use flat .DBF file for our customer database at work. I am going to propose a solution that uses an SQL server but I have some questions for the SQL GURU's around here.

First off, can sql tables have associated indexes and if so, how do you access the information via the index? 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.

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.

I will be doing my developnet in Delphi, not that it should make a difference though.

Thanks for your advice, I will probably have more questions later.

Hrd

edited to add another point

franzelneekburm · 12-21-2004, 08:50 PM

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.

asshopo · 12-22-2004, 07:22 AM

What franzelneekburm said. Only you can also use MySQL, which is free and does it's job well. It's not as full featured as other database servers, but you can find TONS more info on MySQL vs PosgtreSQL (my opinion, not fact). Even better would be to use MSSQL or Oracle should you have access to either of them.

hrdwareguy · 12-22-2004, 08:02 AM

I'll be using MySQL because that's what I'm familiar with and it's free.

Thanks for your answers and suggestions.

12-22-2004, 08:16 AM

if you want to use the indexing thing as you describe it, here's one way of doing it. (I'm used to using MSSQL and Oracle, so my syntax might be slightly different than for MySQL)

Code:

create table phones
(phnid integer,
 phnno varchar(20),
 phnname varchar(20),
 alphasort integer)

insert into phones values (1, '505-4977', 'Tom', 3)
insert into phones values (2, '505-4978', 'Tim', 2)
insert into phones values (3, '505-4979', 'Jim', 1)

select min(alphasort) from phones where phnname => Tim'

will return 2

you can then offer the details associated with alphasort number 2, or add 1 for the next, or minus 1 for the previous.

You will have to reindex the list every time someone enters a new number though.

This could be done using a cursor (if they are supported by MySQL, or programmatically by loading an ordered select and then doing some iterated updates)

It would be handy to do this re-indexing via SQL, but I'm not sure I know how to do that without resorting to stored procedures etc - if anyone does, it would be a VERY useful thing to know - please post!

12-22-2004, 08:18 AM

As for the read/write functionality, you could use a view on the table to provide read only access (but check because some RDBMSs allow updates on views)

hrdwareguy · 12-22-2004, 11:40 AM

The phone number was an example, and to answer franzel's question, yes, the phone number would be treated as an integer so if I had the following numbers:

123-1234
123-1235
123-1237
123-1238

and I searched for 123-1236 it would return 123-1237

other things would be names. Searching on will would return williams, willson etc.

franzelneekburm · 12-22-2004, 01:41 PM

Quote:

Originally Posted by hrdwareguy

The phone number was an example, and to answer franzel's question, yes, the phone number would be treated as an integer so if I had the following numbers:

123-1234
123-1235
123-1237
123-1238

and I searched for 123-1236 it would return 123-1237

other things would be names. Searching on will would return williams, willson etc.

zen_tom's suggestion would only work if the table is changed very infrequently, as the entire table would have to be updated for every insert/update of the phone number.

The way I usually solve similar problems:

Assuming your phone numbers use the same formatting and will sort in the way you want as strings (ie if some are '(123) 456 5555' and others are '123-456-6666' the sorting might not be what you want)

Code:

CREATE TABLE employees (
  employee_id serial primary key,
  phone varchar(14)
);
CREATE INDEX idx_employees_phone ON employees (phone);

you'll need to use two queries to fetch the phones around the nonexistent one

Code:

SELECT employee_id, phone FROM employees WHERE phone > '444-444-4444' ORDER BY phone LIMIT 5;
SELECT employee_id, phone FROM employees WHERE phone < '444-444-4444' ORDER BY phone DESC LIMIT 5;

you can then use either cursors or more queries with different limits/offsets to walk up and down the list.

you'll probably want to normalize the numbers just in case though, one way to do it is to create a function that strips everything but numbers from the string (and returns it as an integer) and then index on that, so:

Code:

CREATE INDEX idx_employees_phone ON employees (strip_alpha(phone));
SELECT employee_id, phone FROM employees WHERE strip_alpha(phone) > strip_alpha('444-444-4444') ORDER BY phone LIMIT 5;

franzelneekburm · 12-22-2004, 01:59 PM

Quote:

Originally Posted by asshopo

What franzelneekburm said. Only you can also use MySQL, which is free and does it's job well. It's not as full featured as other database servers, but you can find TONS more info on MySQL vs PosgtreSQL (my opinion, not fact).

You know, I don't want to start any flame wars here, but I have a really hard time recommending MySQL to people new to databases. There are just so many basic features, which I would consider to be part of good database design, that it doesn't provide.

I use it for some projects, and I like it, but sometimes the things it does to my data just really scare me (this is part of their 'Best Effort' philosophy, I guess it's a subjective call).

As far as documentation goes, I agree that MySQL's is excellent while Postgres' is just very good; but for what a beginner needs both will do just fine.

Quote:

Even better would be to use MSSQL or Oracle should you have access to either of them.

Not necessarily. Certainly Oracle has many advantages over the free alternatives, but usually they only come into the foreground only on massive (and I do mean massive) projects.

I've found that for small to medium projects the extra administrative and development overhead that comes with Oracle is just not worth it. I have a couple of die-hard Oracle fans that came into our mostly Postgres environment, over two years there hasn't been a single "Well, in Oracle I could..." for which I couldn't show an equivalent (or better) solution in Postgres.

Rule of thumb: if your project will need live replication, then think about Oracle; if not, you are likely to just be wasting time and money.

I have no experience with MSSQL, so I can't comment there.

Moskie · 12-23-2004, 04:40 AM

what about stroring the phone numbers as 3 seperate integer values (area code, prefix, suffix)? So the number (410)321-1234 would be stored with an area code of 410, a prefix of 321, and a suffix of 1234. You could then sort by each of those columns...

Maybe I'm missing an obvious downfall, but it seems like it would make the sorting easier.

12-21-2004, 10:32 AM	#1 (permalink)
hrdwareguy "Officer, I was in fear for my life" Location: Oklahoma City	SQL Help I have used SQL before in some very basic and limited fashion. I know how to write select and insert statements but that's about it. We currently use flat .DBF file for our customer database at work. I am going to propose a solution that uses an SQL server but I have some questions for the SQL GURU's around here. First off, can sql tables have associated indexes and if so, how do you access the information via the index? 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. 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. I will be doing my developnet in Delphi, not that it should make a difference though. Thanks for your advice, I will probably have more questions later. Hrd edited to add another point __________________ Gun Control is hitting what you aim at Aim for the TFP, Donate Today Last edited by hrdwareguy; 12-21-2004 at 01:15 PM..

12-22-2004, 08:02 AM	#4 (permalink)
hrdwareguy "Officer, I was in fear for my life" Location: Oklahoma City	I'll be using MySQL because that's what I'm familiar with and it's free. Thanks for your answers and suggestions. __________________ Gun Control is hitting what you aim at Aim for the TFP, Donate Today

12-22-2004, 08:16 AM	#5 (permalink)
zen_tom Guest	if you want to use the indexing thing as you describe it, here's one way of doing it. (I'm used to using MSSQL and Oracle, so my syntax might be slightly different than for MySQL) Code: create table phones (phnid integer, phnno varchar(20), phnname varchar(20), alphasort integer) insert into phones values (1, '505-4977', 'Tom', 3) insert into phones values (2, '505-4978', 'Tim', 2) insert into phones values (3, '505-4979', 'Jim', 1) select min(alphasort) from phones where phnname => Tim' will return 2 you can then offer the details associated with alphasort number 2, or add 1 for the next, or minus 1 for the previous. You will have to reindex the list every time someone enters a new number though. This could be done using a cursor (if they are supported by MySQL, or programmatically by loading an ordered select and then doing some iterated updates) It would be handy to do this re-indexing via SQL, but I'm not sure I know how to do that without resorting to stored procedures etc - if anyone does, it would be a VERY useful thing to know - please post!

12-22-2004, 11:40 AM	#7 (permalink)
hrdwareguy "Officer, I was in fear for my life" Location: Oklahoma City	The phone number was an example, and to answer franzel's question, yes, the phone number would be treated as an integer so if I had the following numbers: 123-1234 123-1235 123-1237 123-1238 and I searched for 123-1236 it would return 123-1237 other things would be names. Searching on will would return williams, willson etc. __________________ Gun Control is hitting what you aim at Aim for the TFP, Donate Today

12-23-2004, 04:40 AM	#10 (permalink)
Moskie Pure Chewing Satisfaction Location: can i use bbcode [i]here[/i]?	what about stroring the phone numbers as 3 seperate integer values (area code, prefix, suffix)? So the number (410)321-1234 would be stored with an area code of 410, a prefix of 321, and a suffix of 1234. You could then sort by each of those columns... Maybe I'm missing an obvious downfall, but it seems like it would make the sorting easier. __________________ Greetings and salutations.

12-22-2004, 07:22 AM	#3 (permalink)
asshopo Insane Location: Michigan	What franzelneekburm said. Only you can also use MySQL, which is free and does it's job well. It's not as full featured as other database servers, but you can find TONS more info on MySQL vs PosgtreSQL (my opinion, not fact). Even better would be to use MSSQL or Oracle should you have access to either of them.

12-22-2004, 08:18 AM	#6 (permalink)
zen_tom Guest	As for the read/write functionality, you could use a view on the table to provide read only access (but check because some RDBMSs allow updates on views)