Database design and SQL help if possible
I am at the start of a new project that will eventually use .aspx with an MS SQL Database. I have put together an Access file to develop the structure.
The finished site will only display information, nobody but me will be altering the DB, so I am concentrating on the select queries first which I thought would be easier.
The database will hold around 30000 client records.
About 10% of those will be premium clients (I hope!).
There will be around 20 categories.
There will be around 10 CCTypes (credit/charge cards).
Each ordinary client will have zero or many categories.
Each premium client will have at least 1 or many categories.
Each client, ordinary or premium, will have zero or many CCTypes.
So after much searching and help file reading I've come up with a database design I think is close to correct.
It's the first time I have dealt with many-many relationships and the SQL to produce results that could be formatted as below is a bit beyond me,
particularly when you consider the following:
The search result should list all premium clients first and in random order i.e. each time the search is run any premium client has a chance to be listed first within the subset, irrespective of name, postcode whatever.
The data will need 'paging' as most searches will return between 100 and 500 records.
The data will be 'cleaned' regularly involving deletion of clients and addition of others (I believe this can cause problems with paging data?)
So my questions (eventually)
1) Is the Database layout right or could it be done a better /different/more efficient way?
2) Can anyone point me in the direction of some good SQL tutorials that cover the above?
3) Can I do the randomising of the records in the SQL query or is it better to programmatically at display.
4) Should I just hire a programmer to write me some stored procedures :D
Any help is much appreciated.
Heres a link while the images are approved.