6,217 reputation
1821
bio website mooseware.ca
location Mississauga, Canada
age 51
visits member for 2 years, 10 months
seen 5 hours ago

I'm a professional software developer with more than twenty years of experience across many industries and the entire systems development lifecycle. I'm the principal consultant at Mooseware Limited.

@joelabrown

google

facebook

linkedin


Jan
30
answered Relationship that are optionally more specific
Jan
30
comment Coding an accounting database from scratch?
Don't bother with databaseanswers.org. The models there are trivial. It may be vaguely intersting to look at these models to see what sort of things might be of interest to a certain problem domain, but the models themselves are so simplistic that they would never come close to being practical for their stated purpose.
Jan
30
answered Coding an accounting database from scratch?
Jan
21
comment What is the way to paginate last updated rows from multiple tables?
@Luccas - I'm not clear on what photo posts are in your system. The bottom line is that if a photo is attached to a post treat it like a comment as far as linking activity. If a photo is a kind of post in and of itself, then do the same thing for photos as for posts.
Jan
20
comment What is the way to paginate last updated rows from multiple tables?
@Luccas - To make it easier to achieve what you want to do, I would keep a column that always points to a post. If the record is a post, then it should point to itself. If the record is something that applies to a post (like a comment) then point to the post on which the comment was made. This lets you attach all activities (at any level) to the posts, which is what you want. If you know exactly how many levels deep your hierarchy can go (i.e. no comment on a comment, etc) then you can also use UNION to combine multiple selects for each depth of hierarchy. Copying the post_id is easier.
Jan
20
comment What is the way to paginate last updated rows from multiple tables?
@Luccas - See my edit. It should explain how to tweak your design and how to access the information you are looking for.
Jan
20
revised What is the way to paginate last updated rows from multiple tables?
Expanded answer to explain implementation details.
Jan
20
answered What is the way to paginate last updated rows from multiple tables?
Jan
19
comment Question regarding 2NF partial key dependency
@ypercube, I thought about that and thought that it was probably a typo based on short-cutting the listing of the original relation (ABCD). It seemed pretty unlikely that D is determined by both AB and BC. Why not write ABC-->D? I couldn't think of a practical case that met the dependencies as stated.
Jan
19
answered Question regarding 2NF partial key dependency
Jan
18
comment Billing Module (Fee) for School Management System
Study double entry accounting and build your system to use that approach. It is proven over centuries and is used all around the world. This is the only design you should consider for financial transactions. The tables that drive pricing can be whatever gives you the information that you need, such as your feeMaster table.
Jan
18
revised Payment methods conceptual and logical model
Fixed terminology
Jan
18
comment Payment methods conceptual and logical model
@Catcall - granted, however, it is "denormalize-ish" insofar as a value is copied across a relationship from what is kind of like a parent to what is kind of like a child. The fact that the copied value is semantically different because it may become non-redundant over time is critically important but a little on the subtle side. I was in a hurry and got sloppy with my language.
Jan
16
comment Payment methods conceptual and logical model
@JankoHrasko - Please see the edit to my answer. I hope that will make it clearer.
Jan
16
revised Payment methods conceptual and logical model
Expanded on the way forward at OP's request.
Jan
16
comment Data warehouse support tickets
I'm not really clear on why you have a star schema data warehouse for support tickets. Looking at your schema I don't see any real advantage over transactional detail. Tickets open on a day is just select count(*) from ticket where created<=@DateOfInterest and closed>=@DateOfInterest. If you want to get open by day, just join this against a number table with dates, no?
Jan
16
answered Payment methods conceptual and logical model
Jan
16
answered creating user related user specific content
Jan
14
answered Is there any reason that metadata should not be a part of my database schema?
Jan
8
comment Maintain table with too many insert
@Tush - See my edit above, providing additional information about how to change the auto_increment field value back to 1.