Parent/child relationships on select 1

[SPrelewicz]

Maybe I'm doing this wrong, but...

I have 3 tables relevant to this problem.

1. Master Users tables
-has USER_ID

2. Username Users
- has USER_ID

3. IP Users
- has user_id

The process goes as: We have 2 ways to authenticate, either IP or Username. I have 3 tables, one stores info on all users (Master Users), with an auto incremented user_id. This user ID is put into either one or the other child tables (Username users, IP Users) with relevant info for each user there. I want to query the db saying something like:

SELECT (either all fields from Master and Username or all fields from Master and IP) FROM (Either Master and Username or MAster and IP) WHERE (MAster.user_id='x' AND
Username.user_id='x' OR IP.username='x')

Any ideas?

Hope this makes sense and thanks in advance for your time.

Scott

 

[sleipnir214]

Could you explain in greater detail the credentials your script will be presented with?

Am I correct to assume that if the user connects from a specific IP address, the user should be automatically authenticated, and a username retrieved?

Or, if the user does not have an IP-specific login, he will provide additional credentials? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

>>Or, if the user does not have an IP-specific login, he will provide additional credentials? <<

This is correct. The script will check the users IP and if it is coming from one of our institutions, will authenticate with no username.

If the IP does not fall within one of our ranges, he or she is then presented a login screen.

The reason for three tables.

1) Master keeps all info that both types of authenticates have (User ID, who affiliated with, date account created)

2) Username keeps info specific to those that have an account (Username, password)

3) IP keeps the IP account type specific info (IP ranges)

Hope this helps, and thanks for the quick reply.

 

[SPrelewicz]

I think I should add that the SELECT I need is not necessarily at the authentication process. We also have an admin section. What I want to do is have an admin app that covers IP and Username accounts. First it'll display either username or IP accounts, each with their user_id as a parameter. Then a click will bring up that account info-Either all the fields in the master table and Username table or Master Table and IP table depending on which type it is. Example:

(Tables shortened for brevity sake)
---Master---
user_id affiliation etc..
1 CHOB
2 MFS
3 HRC
4 CHOB
5 HTC


---USERNAME---
user_id username etc...
2 jschmoe
3 pheaton
5 e_costello

---IP---
user_id low_IP high_IP
1 123.456.789.098
4 123.876.543.212

So when an ID is clicked, it will pull up all info in master, and either the rest from Username or IP,
using the user_id to figure out which one.
Do I need some kind of lookup table maybe, ACCOUNT-TYPE, that has all user_id's and like 1 for username and 0 for IP so it knows which table to go for the rest?

Thanks again.

 

[sleipnir214]

It would seem to me, then, that trying to get all your information at once is working yourself too hard for no reason.

Have your scripting language perform the master/ip check against the connection IP address. If it succeeds, set whatever user login token you're using.

If that previous check fails, present the login form. When that form is submitted, perform the check against master/users. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Even though I sent that before, you're answer I think helped me solve my problem.

Ugh, what a simple problem I made so difficult.

Since the Username and IP account listings in the admin section are separate, I will send a flag with the id, like type=IP or type=username and have Perl decide which Master/x to do.

I guesss I was caught in the trap that since each user_id in the child tables was matched with 1 and only 1 in the parent, I should do it that way. Of course, there's always something simpler.

Thanks again.

 

[sleipnir214]

Now that I think about it, why do you have three tables? Why not add to master the columns IP, username, password, where each of the three can be null?

You either fill in the IP column and set userid and password null, or fill in userid and password and leave IP null.

Your query for authentication is still two-part, but the queries become a lot easier to manage.

Your program that builds your admin interface can then just pull up all master records and display them in a manner that is pleasing to you. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

I suppose I could do that, but there are many more fields specific to each type. IP's don't have names, emails, telephone, etc...and Usernames don't have Tech contacts, etc...

At are site, local doctors can connect via IP when at work at the hospitals, but not from home, where they need a username/password account. Each hospital has a range of IPs and a contact person for that range, etc...

 

[sleipnir214]

That internal authentication system is, frankly, screwed up.

You have no way of telling who accessed your system internally, and only a vague notion of from where.

I sincerely hope that these doctors aren't doing anything with patient records. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

No, no record stuff, only articles from journals and stuff like that. I would agree with you here. Unfortunately, the higher ups insist on the IP route. I would like to have everyone have to authenticate. It's 10 seconds more time.

 

[sleipnir214]

The minute anyone talks about using this authentication with anything important, you'd better scream &quot;HIPPA! HIPPA!&quot; at the top of your voice.


A particular user, when accessing internally, will do so from a range of IP addresses, or must he log in from a single specified one? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

There are ranges given to us by each Hospital. We are non profit, affiliated with the Medical Dept. of the local University and all the Hospitals in the area, so the range comes from every single subnet at the university and each IP at a Hospitals library. So in some cases it's a range if IP's (IE, at the University EVERY IP) and in some cases a single specified one (the smaller Hospitals have only 1 computer accessible in it's library)

It's a complicated situation, in'it. Just everyone UN/PW authenticate, so fewer problems. But the board insists.

 

[sleipnir214]

Ohhhh!

You're not writing this for as part of a hospital IT department. You're an information clearinghouse to multiple hospitals.

So a range of IP addresses may only have 1 address in it.

I assume, then, that you are only authenticating users, not identifying them, right? Once good credentials are presented, you don't need to differentiate one user from another, right?

I see no reason why you couldn't split the selects in your admin section. But if you want, using a LEFT JOIN to join the tables together should do it. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Exactly. We do keep track of login by institution, however, and will by username, but these stats are not vital. The important stats are the institution stats becauyse they get charged by how active they are (Also, different institutions receive different kinds/amounts of information) I guess I should have been clearer!

I will look into the LEFT JOIN. This will join all three tables, correct? However it won't matter because the columns where the user_id doesn't match will be empty.

Thanks for all the help. I wasn't a DB person, just gratuating with a CS degree, but am learning on the fly

Thanks again,
Scott

 

[sleipnir214]

Your CS curriculum didn't include a class in databases? How odd. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Not a one. There's a separate MIS department there that is geared toward that. CS is all programming and theory and junk Very little practical stuff.

 

[sleipnir214]

So was mine. And we have a course on databases, which includes set theory, normalization, and all that fun stuff as well as having to program using what you've learned. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Yeah, that would have been nice to have had. I can say that through my 5 years there (Univ. of Buffalo, by the way) I probably didn't hear the word database mentioned once. Well, that may be an exaggeration, but still, it was hardly touched on.

 

[sleipnir214]

Mine's the University of Louisiana at Lafayette. Here's the course description from the online catalog: &quot;Introduction to database design and implementation. Information level design techniques. Database models: network models and relational model. Normalization relational languages, physical storage and indexing structures, query processing and optimization, crashes and recovery, security and integrity constraints, distributed databases.&quot;

You haven't had fun until you've tried to implement a database engine from scratch -- maintaining concurrency kills you every time. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!