Where I work there's an access database which contains a list of customers, the number of time tokens they've bought (Since we took them on as a customer), the number they've used, and a log of what was used and when. It's a really **** system. There's a "Search" button that doesn't work, and you have to manually scroll through ~60 records. It's not very fun. It currently looks like; when you look at the "database", and; when you query it. I've taken it upon myself to migrate to MySQL - I may not know much about database systems, but I know it has to be better than MS Access. I'm not overly fussed about migrating the data from Access to SQL, because it's done so poorly in Access, I don't want to migrate that cluser-**** into the new system. Where I'm stumbling is linking the tables in the SQL side of things. My current thoughts are something like: But I don't know, really, where to go from here. I need to work out the best way to store the "log" of activity as it were, the date that activity was logged (If I can automate that, I would much prefer to. I assume this is something I'd need to do with PHP or C# depending on which way I go about it), who logged it (We're in the habit of putting initials in the start of the log), and an editable display of current number of tokens with an editable display of used tokens (Something else, I assume, would be down to PHP or whatever pulling those records from the db). Ideally I want to come out the other end of this with an interface similar to the access one above, I'm leaning towards an intranet page base for this. What can MySQL do in terms of large blocks of text? How do I then link a Customer record to a TotalTokens/UsedTokens and an Activity Log record? Is SQL even designed/capable of doing something like this? (I'm assuming yes) I'm using MySQL Workbench at the moment, I've looked around at different products that do the same thing, and I can't seem to get along with them as well as Workbench.
Almost right - you've got your key relationships a bit confused though; the way you've written it you'll have multiple customer records linked to multiple activities/tokens. You need a single 'anchor' table with multiple related tables to do what you want. I'd suggest: Table customers customer_id INT(11) primary_key (unique, auto_increment) name VARCHAR(255) creation_date DATE index Table activity activity_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index creation_date DATE index description TEXT Table tokens token_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index tokens INT(11) index creation_date index Now to track how many tokens were used - here's a question, what uses up a token? Is it 1 token per activity? Is it based on the amount of time spent? Answer that and I can suggest you some logic to work the rest out.
The basic premise is they buy blocks of time at a discounted rate, but the time is only ever used in half hour increments. So realistically there's only ever going to be a number there, with the used tokens field being updated whenever a job is done, and the total tokens being updated as and when the customer buys a new "pack". I figured I had the single-many type thing in the wrong way, but I was hitting my limits on guesswork anyway, so I stopped to ask
Okay - either you need 1 activity = 1 token or you need to ensure that someone records how many tokens an activity used. I'll assume you give the power to your techies to accurately record the number of tokens used, so I'd recommend this: Table customers customer_id INT(11) primary_key (unique, auto_increment) name VARCHAR(255) creation_date DATE index Table activity activity_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index creation_date DATE index description TEXT tokens_used INT(3) index - this will be limited to 999 tokens, but that sounds plausible right? increase if not Table tokens token_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index tokens INT(11) index creation_date index Your SQL query to display the customer and dynamically count total tokens purchased/used is: Code: SELECT cus.customer_id as cus_id, cus.name, cus.creation_date, (SELECT SUM(tokens_used) FROM activity WHERE customer_id = cus.customer_id) as tokens_used, (SELECT SUM(tokens) FROM tokens WHERE customer_id = cus.customer_id) as tokens_available FROM customer cus ORDER BY cus.name Your SQL query to display (optionally a single customer) list of activities Code: SELECT cus.customer_id as cus_id, cus.name, cus.creation_date, act.activity_id as act_id, act.creation_date, act.description, act.tokens_used FROM customers cus LEFT JOIN activity act ON act.customer_id = cus.customer_id [I]optionally[/I] WHERE customer_id = 'my search customer id from a search box' [I]/optionally[/I] ORDER BY cus_id, act.creation_date Alternatively if you want to make one activity equal one token, delete the tokens_used field and instead do COUNT(activity_id) instead of SUM(tokens_used) in the first SQL query. There'll probably be some errors in this as I'm not the most amazing DBA around here, but it should be close. When a customer runs out of tokens, you don't edit the number of tokens, you instead add another record with the date and number of tokens they bought. That way you get an audited history of how often they buy and how many they use over a period of time.
I think your problem here is not with Access but with the designer of the database. When dealing with relational databases, you must make sure the tables at the backend are designed properly before attempting to do the frontend. To answer your question of how to store the log of activies, Zoon has already come up with the answer, but I would also add the attribute logged_by: Table Activity activity_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index description VARCHAR creation_date DATETIME duration_of_activity TIME tokens_used INT - may be a calculated field from duration at implementation logged_by ENUM('JW',SM','DE','TS','PD') I would also add to the Tokens table: Table Token token_id INT(11) primary_key (unique, auto_increment) customer_id INT(11) index type - I saw you had something like General and Annual amount INT(11) start DATETIME end DATETIME So with an activity table, you have no need of a large block of text anymore. By joining tables Customer and Token, we can calculate the total amount of tokens bought since joining, the amount of unexpired tokens etc. By joining Customer and Activity, we can calculate to total amount of tokens used since joining or for this year so far etc.
Thanks, Zoon. Based on your posts I've got this so far; Tor some reason it took some buggering about to get it to link the tables like that, but hey. Sourdough: I'd agree that it was the designer rather than access - But another aspect to this is that every machine we buy for ourselves needs a copy of access (Which, apparently, doesn't exist in our action pack, and thus means we have to buy another copy of office), so moving away from access to an intranet site would save me (since muggins here has to do all the installs) some hassle. Adding the fields you suggest to the activity field, would that mean the description field is just a description and we don't have to enter the date/initials to the activity field? My assumption is "yes", but MySQL and I don't seem to be on the same page! The type thing, I don't believe that we use that any more. The expiration date isn't for all of them, either. Some customers are a little less willing to phone when they have an issue because they're in the mindset that they should "save the time tokens just in case", which inevitably leads to more issues down the road when they do eventually call - So we sell them with a year or two year (Depending on the number bought) expiration date to encourage them to call and get things sorted out.
Activity is not a field any more, it's an entity (or a table) on it's own which has attributes like description of the activity, when was it created, how many tokens was used and who logged the activity. A typical row (record) of an activity would look like: Code: activity id, customer_id, 13-03-08, 020000 (in time format), TS, "issues with server down and followed by AV not updating" activity id, customer_id, 28-05-08, 050000 (in time format) , DE, "rebuild Martin's PC after he pressed F11 to do factory reset" ENUM is a enumeration data type. It's usually presented as a select list at your front end. This field is currently a one-to-one relationship with Activity, you would not be able to associate an activity with more than one person like TS/PD. If you need to be able to track both employees, then you would need to break out this field into a separate table (normalisaton) in order to implement a one-to-many relationship.
What front-end interface will you be using for the users of this system? I guess what I'm really asking is what powers your intranet server? Apache/PHP? IIS/ASPX? Sharepoint? Generally you can capture the Windows (domain) username of your users automatically and use that to tag each "Activity" with.
Ah, I see. I think. Zoon: It's an sbs2008 domain, although I've not gone much deeper in that regard, I want to make sure I can get the SQL system working first.
Okay - you can't really roll out the SQL administration tool to replace Access though, you need to code some kind of front end to programmatically update. PHP is usually the easiest to use with MySQL, although there is an ASP.NET connector for it too. You could make it a desktop application that way if you wished, although its much simpler if you just make it a webpage. If you have Sharepoint you can use Sharepoint Designer to make this system a Sharepoint application fairly easily too, which is why I asked about that - although I'm not a Sharepoint developer so I can't provide much guidance in that area unfortunately.
I'm thinking, because the other techies here are paranoid, it'll be easier to do a webpage. I've been piddling about with sharepoint 2010, but the differences between it and 3.0 are dramatic - There doesn't seem to be a way to do it with 3.0
Considered just using sql server express & continuing to use MS access for front end forms? look into access data projects in the MS access helpfile. Might be a less painful/risky solution. Though, looks to me like the form design is the problem rather than the db host. There will be some fundamental behaviour differences between using an active rdbms over ms access irrespective of what you choose to manipulate the data. Also worth checking out the featureset of both & making sure that it's the right tech. I'd never considered mysql as a valid epos/multiuser rdbms, as the use cases can be quite complicated & transactional integrity is pretty important whereas for serving up data for stateless web-pages MySQL is perfect. I'm assuming the company is not publicly listed, else you'd be concerned with SOX as well, security is a key element for customer data & at least with ms tech you can secure your db without having to write anything into your app for that.
Landy_Ed knows more than me about this stuff - SQL Server Express is free so you may as well start as you mean to go on. If getting rid of the requirement to license Access is a part of the business case for doing it at all then you'll obviously still need to look at building your own frontend for it though.
