|
May I suggest a 3rd option?
Use 2 tables. Combine the auth_users and members table. Add a new table called user_props. user_props will need 3 columns.
pkid (ref auth_users)
prop_nm (char 50?)
prop_vl (text)
In the first table, plan on just keeping the absolute minimum # of fields you will need for about any system. (PKID, USERID, PSWRD, ACTV) may be enough.
Define your own prop_nm values and place all other user related details here, stored vertically. (Ideall you will have a 3rd table which has at least 2 columns and defines the prop_nm values you create.)
You will only ever need 2 queries and these should be encapsulated into a single ASP module, C# class or whatever. You will be able to add new properties whenever you want (there will be more, count on it.)
I use this tecnique for user definition and it has payed off many, many times. I don't recommend using this tecnique for just any data but for user login info, it is great.
Just my 2 cents.
|
|
|
|
|
Could I not store all account information inside a members table (access privileges, account expires, etc..) and only username and password inside auth_users. I'm thinking if I could then use an index on auth_users MemberID to quickly select the member information, this would be quite the optimization would it not?
i'm a total newbie with mySQL, so please forgive me if my terms are off.
]Thanks 
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
Sure you could do it this way. The easiest way if you want a 1:1 relationship between auth_users and members is to either use the same PK or add a column to one or both tables mapping to the other tables PK. The downside to using the additional column in only one table is that you will not be able to do a reverse lookup from the other table. The downside to using an extra column in both tables is that you will have to keep their values in-sync.
If this is the method you are looking at, I would recommend that you set it up so that their PK fields are the same. This way there is only 1 field to keep in sync and since it is the PK of the table you won't ever have to worry about it.
The method that I recommended was intended to make the user-solution easier to adapt to new requirements (account expiration, lockouts, limit to IP address, require SSL, log all activity, admin privilidges, etc). My experience has been that user authentication and user management is always a moving target. As a system becomes more complex, accessible or just management changes its mind, there tends to be more and more requirement for access control, authentication and logging mechanisms. It is virtually impossible to predict those requirements in the beginning.
The solution I recommend coped with this by proving a flexible property assignment mechanism that would make the DB schema and related code relatively imune to requirement changes. You still have to deal with the code that actually implements user-rights, logging, expiration, etc.
|
|
|
|
|
Matt Gullett wrote:
The easiest way if you want a 1:1 relationship between auth_users and members is to either use the same PK
I didn't even think of that...thats a great idea...because there will always be that one to one relationship. The solution I was looking for was more optimization than anything, not ease of implementation. Using the above one to one relationship model, is it possible to query only the authentication table ('auth_users') initially using something like:
SELECT * FROM auth_user WHERE user='Test' AND pass='Test'
Ideally I would like to use the PKID of the resultset returned from the above query and somehow use another query to return the associated ('members') table record. It's to my understanding that my default SQL(mySQL) automatically uses indexes on primary keys??? So really whats happeneing when I call the above select statement the record is being being indexed, rather then being located sequentially? I don't know if i'm accurate in thinking this or not.
I wouldn't care so much if the ('auth_users') table was sequentially searched becuz it's small in size. However the members table will be quite large so to have a query sequentially search each record here would be quite redundant. I could just make one query if I stored all this data in one table. Basically I would like to know if the following:
SELECT * FROM auth_users WHERE user='Test' and pass='Test'
and then
pkid = auth_id;
SELECT * FROM members WHERE pkid = auth_id
would cause the second select statement to use indexing by default so to avoid sequentially iterating records in the members table again. This to me seemed like the optimized approach, seeing how auth_users is a small table, so using it to index the equivilent record in the members table made sense. However i'm rather new at mySQL/SQL so i'm not sure.
I was hoping you could shed some light on this subject for me?
I appreciate all the help so far.
Cheers!
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
Hockey wrote:
is it possible to query only the authentication table ('auth_users') initially using something like:
YES.
Hockey wrote:
Ideally I would like to use the PKID of the resultset returned from the above query and somehow use another query to return the associated ('members') table record.
You could do it this way, but typically you would just want to use a single SQL statement with a JOIN to get both the data from the auth_users and members table all in one statement.
SELECT A.PKID, A.USER, A.PASS, A.WHATEVERELSE, B.WHATEVER FROM auth_user A, members B WHERE A.user='Test' AND A.pass='Test' AND B.PKID = A.PKID
I don't know much about MySQL but every DB I have every worked with supports at least limited JOINS.
As far as the indexes go, every DB I have ever worked with maintains an index on PK fields. Leveraging the indexes a DB uses is the best way to insure performance. (Understanding how indexes work, what to index, etc is also pretty important, but for our discussion, this is enough.)
|
|
|
|
|
I just want to make sure I understand this correctly then (I really appreciate your help BTW).
With the code you provided above, the implicit JOIN, does the query use an index for both tables? I wanna make sure I don't sequentially iterate each record (especially on the members tables) if not nessecary. That is of course assuming my understanding of indexes is correct, but I think it's basically like a pointer.
So long as the above query uses an index on atleast the member table i'm happy.
Thanks
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
I can't say that I'm 100% sure how MySQL will deal with this, but every DB I know of can handle at least 1 index per table involved in an SQL statement (with certain rules). Assuming MySQL at least provides for this (many DB's support many more indexes per table in SQL statment), YES it will use the indexes.
I know it sounds like I'm hedging my bet here, but the answer to your question should be YES unless MySQL really, really sucks. (From what I do know about MySQL it is designed for performance and this kind of optimization would be pretty important to performance.)
I have written an optimizing SQL parser/analyzer and I can say from experience that the most likely thing to happen is that the parser/analyzer will see that your SQL statement is using a PK (unique index) as part of the where clause (B.PKID = A.PKID) and will be smart enough to know to use th index. I'd be really surprised it it did not.
|
|
|
|
|
All i needed to hear. Atleast now I know the question would probably mysql specific and will ask if mysql supports multiple indexes in a single statement.
Thanks again!
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
How long will an ADO connection object remains connected to SQL server if there is no communication at all from the client side??
|
|
|
|
|
Forever. Well actually until either the SQL Server terminates it or a network problem occurs. A default configuration of SQL Server will never expire a connection, but some configurations will.
That's my experience with it anyway.
|
|
|
|
|
I have an RecordSet object.How can I figure out if it is Open or Close now?(Using ADO)
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
RecordSet.State is your friend.
http://www.devguru.com/Technologies/ado/quickref/recordset_state.html[^]
"The State property returns a long value that is the sum of one or more ObjectStateEnum constants. These constants describe if the Recordset object is open, closed, or executing an asynchronous operation.
You can call the State property at any time. The default value is adStateClosed."
So in VBScript you could test it like
If oRecordSet.State = 1 Then ...
Or in JScript you'd use
if (oRecordSet.State == 1) ...
The ObjectStateEnum constants are explained on the page I've linked to above.
[edit]
Actaully I suppose a recordset could be both open and executing or fetching (could it?) so you may need to test for that possibility.
|
|
|
|
|
hmmmm,thanks.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
can some tell me what is the best way to count the records in MS access database based on WHERE statement?
Jassim Rahma
|
|
|
|
|
If you're using ADO, this may help:
RecordCount Property
Indicates the number of records in a Recordset object.
Return Value
Returns a Long value that indicates the number of records in the Recordset.
Remarks
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.
[From the SDK Documentation]
As I read it, you should be able to select your recordset using the WHERE clause, then use rstMyRecordSet.RecordCount() to return the number of records it contains.
"How many times do I have to flush before you go away?" - Megan Forbes, on Management (12/5/2002)
|
|
|
|
|
Well from your question I read two things you might be wanting to do:
SELECT Count([column]) AS [NumRows] FROM [Table] [[WHERE [Condition]]
The count is returned in NumRows. If you need to specify additional rows to return then you'll need to look into grouping which should be in the MS Access documentation.
Or are you wanting to include a row count within another condition? E.g.:
SELECT * FROM [Table] WHERE "some table's row count is equal to 4"
Skippy, the rain won't come! [+]
|
|
|
|
|
Can some tell me about his experience using mySQL with .NET? what are the advantages and disadvantages? any limitations or restrications?
I want to build a corporate application which will have a network access as well as remote access and I'm planning to use mySQL because of the limitation inb MS Access and SQL server with MSDE.
Jassim Rahma
|
|
|
|
|
Before you go developing a "corporate level" application using MySQL, you should spend some quality time reading the Reference manual that's available both on-line, from the MySQL website, and as part of the MySQL installation. I just checked their website before writting this reply, and found that as of their 4.0 Gamma release (not production), MySQL is still lacking support for both Stored Procedures and Triggers. They "expect to have it in version 5.0", but they do indicate that they are "looking into it", and not that "they will do it". Additionally, their 32-bit ODBC driver is still considered a Gamma release and conforms to the ODBC version 3.51 spec, and not the newer 3.52 spec. These are fairly signficant components to a mission critical application, if you want a true Client/Server application.
I have to assume that you are not considering SQL Server an option because of price, so I'll suggest that if you want a true Client/Server database consider Sybase SQL Anywhere (also called iAnywhere). I've experience with both MS SQL Server 7.0 and 2000, as well as Sybase 6.0 and 7.0 and I have to admit that Sybase is stable, fast and powerful, handles transaction processing, can be used with MTS, has a small foot-print and doesn't hurt the ol' pocket book.
Just my two cents, I'll sign off now, because I'm beginning to sound like a commerical for Sybase ...
Good-luck.
D.
|
|
|
|
|
what about SAP DB?
Jassim
|
|
|
|
|
You make a valid point about the lack of certain powerful features, but if you want power and scalability in a small, inexpensive, easy-to-use database, you can't beat MySQL. I have been frustrated, from time to time, with not having transactions and stored procedures, but I've built complete apps that work well and serve their purpose with handling those kinds of things in my application code. It's probably not the perfect solution for everything, but talk to the guys at slashdot.org about power and scalability and they'll show you 50 million pages served per month!! You can read more about the slashdot setup here Just my 2 cents too.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
What limitations of SQL server(Msde) are stopping you from using it? 2GB database size maximum?,no Replication? In a "Corporate" app, I wouldn't think the cost of SQL would be that big an obstacle (and would probably ony be there if you expected more than the 5 concurrent user that MSDE is tuned for).
Some ideas are so stupid that only an intellectual could have thought of them - George Orwell
|
|
|
|
|
yes, limitation in number of users is the major problem for me. I have 8 users now and expected to 18 in few months. what is your suggesstion?
Jassim
|
|
|
|
|
I think it depends on how database centric your application will be.
MySQL appears to be very fast and works fine with .NET (just like SQL Server does), but its lack of views, stored procedures and triggers may be the factors that stop you using it.
Dave.
|
|
|
|
|
Hi all,
I've been working on a conversion app, that is a one off application... basically reading data from one table and inserting it into a new table. I'm using AppendChunk to insert the new record into the destination table... now on to the problem.
When I append the data to the field it seems that this is being done asynchronously and when I try to free the memory I’m getting an exception that says the memory is locked. Here's the snippet of code: This code is inside a try/catch block
<br />
...<br />
_variant_t varChunk;<br />
varChunk.vt = VT_ARRAY|VT_UI1;<br />
varChunk.parray = ChunkBlock;<br />
<br />
pRs->Fields->Item["Data"]->AppendChunk(varChunk); <br />
<br />
hr = pRs->Update();<br />
pRs->Close();<br />
SafeArrayDestroy(varChunk.parray);<br />
...<br />
My questions are; is AppendChunk an asynchronous operation? And if so, is there a way to force it to be synchronous?
Thanks for any help or pointers...
Dave "Dak Lozar" Loeser
When access is allowed to a member, it said to be accessible. Otherwise, it is inaccessible. - MSDN:C# Programmer's Reference
|
|
|
|
|
guys..can someone explain or lead me to some good documentation
that shows that manipulating the DB from a COM component is more scalable
than accessing the DB directly from an ASP page using ADODB ?
Ofcourse, it is understood that using a component has advantages like
object/connection pooling, re-usability, cleaner design etc etc..but my concern is whether
by putting the database logic in a component, it would be able to manage a
larger user base i.e does using ADODB.dll directly from ASP have a limitation to the no of users
that it can support ??
There are no failures; there are only extended learning opportunities.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
