meta user
network profile
| bio | website | mooseware.ca |
|---|---|---|
| location | Mississauga, Canada | |
| age | 50 | |
| visits | member for | 1 year, 8 months |
| seen | 3 hours ago | |
| stats | profile views | 132 |
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.
|
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. |
|
Jan 8 |
revised |
Maintain table with too many insert Added information about reseeding in response to OP's comment. |
|
Jan 7 |
comment |
Is a surrogate key better than a natural key in this case @Songo - Various DBMS allow you to specify how much empty space to keep in your data and index files. SQL Server calls this "Fill Factor". Other DBMS could have other terminology. When you insert a record it has to go into the right place according to your clustered index. If your record goes into the middle of the table and there is some free space, then it's easy to just write the record where it needs to go. If there isn't free space, then the DBMS needs to move everything after the insertion point to make room for the new record. That can be very slow, especially in big tables. |
|
Jan 7 |
comment |
Is a surrogate key better than a natural key in this case @Songo - Unless you have very specialized performance requirements, I would design for maintainability and usability rather than for performance. There are no hard and fast rules for what is going to be faster. It depends on the distribution of the data values and which values you are looking for (and how often). In the end you can often only tell by measuring with production data at production volumes. One factor that is likely to be important is that covering indexes are generally fast. Filtering by country="CA" will probably be faster than joining to a code table with an int FK. |
|
Jan 7 |
comment |
Is a surrogate key better than a natural key in this case @Songo - Yes, page locks on data and index pages will cause transactions to line up. If the lineup is long enough, you may even get timeouts. That is what a "hot spot" is. |
|
Jan 7 |
answered | Is a surrogate key better than a natural key in this case |
|
Jan 7 |
comment |
Is a surrogate key better than a natural key in this case @Songo - Certain "code table" values, especially those that are determined by a standards body, can be reasonable natural keys. Think of "M" or "F" for gender, and things like currency and country codes. Otherwise, surrogate keys are very often safer and better. |
|
Jan 4 |
comment |
Database Design - One table from many source(One to One) @Ahmad - The way I would handle there being one kind of subtype that is different from the others is to keep the columns (and also distinct related entities) in just the subtype. Therefore, if your CSV/Excel subtype needs a child table to track multiple entries, then just keep either an empty value in Text.content or some reasonable summary value, like the contents of the first cell, or a placeholder like "(See Details...)" or whatever suits your needs. |
|
Jan 4 |
revised |
Database Design - One table from many source(One to One) Expanded to include note about the need for non-declarative constraints. |
|
Jan 4 |
comment |
Database Design - One table from many source(One to One) @miracle173 - Agreed, the partitioning attribute is derivable and requires extra code to ensure consistency if it is to be persisted at the super-type. Thanks for the +1. I'll update my answer to reflect your comment. |
|
Jan 4 |
answered | Database Design - One table from many source(One to One) |
