I have a query which is starting to cause some concern in my application. I'm trying to understand this EXPLAIN statement better to understand where indexes are potentially missing: +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+...
I am in the middle of designing a system that will be used to feed several production sites around the country (all information is in one site) with the potential to add more. Initially I thought that I could get away with only using one database. I am now re-thinking my original design and leaning toward a more scalable solution. Kee...
Hi, I'm about to enter the 1st phases of designing a commodities trading application. A major function of this application is the capture and evaluation of pricing information. Pricing in this market is not fixed. It is usually a market benchmark +/- a premium. For example: (A and B are market benchmarks) Price = A + $1 Price = A + .5...
Hi, I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use. Since supposedly char columns of a fixed width are more efficient, I was thinking the same could be true for decimal columns. Is it? And what precision and scale should I use? I was thinking precision 24/8. Is...
I have to develop a system to monitor the generation/transmission of reports. System data will be stored in database tables (Sybase) Reports will be generated with different schedules ("mon-fri 10pm", "sat 5am", "1st day of the month", etc.) System will just monitor that the reports were created. It will not create the reports itself....
In order to handle concurrency issue, is locking-- any form of locking, whether it's row, table or database locking a good solution? If not, how to handle concurrency issue? ...
Hello, I have to add a coupon table to my db. There are 3 types of coupons : percentage, amount or 2 for 1. So far I've come up with a coupon table that contains these 3 fields. If there's a percentage value not set to null then it's this kind of coupon. I feel it's not the proper way to do it. Should I create a CouponType table and h...
Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them? ...
Suppose a database table has a column "Name" which is defined as key for the table. Usual name values will be "Bill", "Elizabeth", "Bob", "Alice". Lookups on the table will be done by the name key as well. Does hashing the values optimize the operations in any way? i.e. entering each name as some hashed value of the name (suppose MD5 - ...
Hello, I have a Coupon table. A Coupon can be applicable to certain items only or to a whole category of items. For example: a 5$ coupon for a Pizza 12" AND (1L Pepsi OR French fries) The best I could come up with is to make a CouponMenuItems table containing a coupon_id and bit fields such as IsOr and IsAnd. It doesn't work because ...
I have the need to create the functionality to allow my registered users to associate themselves with other uses and create a family. My database tables look something like this: db.persons - (Bob, Jane, Timmy, etc) id name email db.persons_persons id person__id_parent person_id_child relationtype_id db.relationtypes - (spouse, ...
Would like to get a list of advantages and disadvantages of using Stored Procedures. The main advantage of SPs seems to be precompiled and an abstraction of data from the application. Give me your thoughts.... ...
We've got data with two different origins: some comes from a customer, some comes from different vendors. Currently, we physically "merge" this data into a massive table with almost a hundred columns, tens of thousands of rows and no formal separation of the two dimensions. Consequently, we can't actually use this table for much. I...
Hi. I am part of a team creating a web application using PHP and MySQL. The application will have multiple users with different roles. The application will also be used in a geographically distributed manner. Accordingly we need to create an access control system that operates at the following two levels: Controls user permissions fo...
We have been working on a complex database and client interface for the last 18 months. We are regularly adding new functionnalities to this application, and it is now used by tens of users on a daily basis in all our offices, including sites and overseas. This is just to tell you it is a REAL application with a REAL database. Until now...
How can I efficiently create a unique index on two fields in a table like this: create table t (a integer, b integer); where any unique combination of two different numbers cannot appear more than once on the same row in the table. In order words if a row exists such that a=1 and b=2, another row cannot exist where a=2 and b=1 or a=1 a...
I've worked with MySQL and MSSQL for some time and have used a variety of CASE and UML tools when designing some of my more complex projects. I was recently asked by a colleague if I could provide an Access database for his department. The application itself isn't too complicated, and Access actually looks like the ideal tool for the ta...
When I am creating a new database table, what factors should I take into account for selecting the primary key's data type? ...
Hi Everyone, What's everyone take on tieing code enum's to row ID's in a database table? I'm really looking for a cleaner alternative. What if for example your static rows in a given table are ID's 1,2,3, and then that table gets filled with user transactional data using ID's 4-100, and then you later want to add a new row ID which in y...
I'm working on a team investigating a technology stack for a green-field systems integration project. Part of our favored technology stack use Hibernate to persist its internal state and exposes hooks into the Hibernate transactions so that business entities stay synchronized with the state of this utility. Great, except we need to maint...