Hey, I'm trying to figure out what would be the best way to have a history on a database, to track any Insert/Delete/Update that is done. The history data will need to be coded into the front-end since it will be used by the users. Creating "history tables" (a copy of each table used to store history) is not a good way to do this, sin...
I know there are a large number of CASE tools out there, some better than others, but what are the ones used by those in the field, and why do you use those specific tools? In this case, I am just interested in CASE tools for database design, and more specifically, ones that will let me "draw" the schema diagram, and then will create th...
I have a database that has two tables "locations" and "classes". I am familiar setting up some sort of parent/child situation with my database tables, but this one has thrown me for a loop. This relationship works exactly like you'd expect. The "locations" have multiple "classes". But some of the "classes" are at multiple "locations"...
I was experiencing a hard time creating FK relationships in my relational SQL database and after a brief discussion at work, we realized that we have nullable columns which were most likely contributing to the problem. I have always viewed NULL as meaning unassigned, not specified, blank, etc. and have really never seen a problem with t...
In a lot of databases I seem to be working on these days I can't just delete a record for any number of reasons, including so later on they can be displayed later (say a product that no longer exists) or just keeping a history of what was. So my question is how best to expire the record. I have often added a date_expired column which i...
Hello, I'm a part of a team writing an application for embedded systems. The application often suffers from data corruption caused by power shortage. I thought that implementing some kind of transactions would stop this from happening. One scenario would include copying the area of a file before writing to some additional storage (trans...
I have a service application that monitors a directory changes. The service application will monitor the directory closely. Here's how it does the thing: Sleep X minutes Scan through the directory, see whether there are new additions Launch one thread per one addition Repeat 1-3 One of the thing it does is that it will insert a reco...
I was thinking about some stuff lately and I was wondering what would be the RIGHT way to do something like the following scenario (I'm sure it is a quite common thing for DB guys to do something like it). Let's say you have a products table, something like this (MySQL): CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increme...
Hello everyone! I am making an SQL database that stores contacts. I want to be able to delete contacts, and the correct id for each contact is crucial for my software connecting to it. Lets say I have contact James and he is the first one. His id is 0. I add Mary and her id is 1. If I delete James, how can Mary's id be set to 0 instead o...
If a DB is designed as: Table A a (type x) a is (PK) Table B a (type x) a is (PK) Table C a (type x) a is (FK) references A(a) a is (FK) references B(a) Is this correct design? If not, what's the best design for this case? ...
I was wondering if it is possible to create a super-type sub-type relationship in Oracle Designer. I would like to create something like this: Thanks. ...
Are there any open source applications that demonstrate good techniques of online-offline synchronization of databases, something like Remember The Milk does with their iPhone app, Google Gears offline mode, etc.? ...
Is it possible for MySQL database to invoke an external exe file when a new row is added to one of the tables in the database? I need to monitor the changes in the database, so when a relevant change is made, I need to do some batch jobs outside the database. ...
I'm trying to decide on the best way to model a relationship of records in a relational database. It's the classic friend/follow model: ~~~~ A User can have zero to many friends. A User can have zero to many followers. Friends and followers are both Users themselves. ~~~~~ What's the best way to model this? Thanks! ...
I've got quite a long business process which eventually results into financial operations. What matters in the end is quite exclusively these final operations, although I've got to keep a log of everything which led to it. Since all the information contained into the final operations is available in other tables (used during the busine...
I know, I know, putting two related tables on different databases isn't exactly the best design practice. But for whatever's sake, suppose that I have to do it absolutely. And I have to break up two foreign-key-related tables that were previously located in a database into two databases, that are located on two different servers, but I s...
I have an order which has a status (which in code is an Enum). The question is how to persist this. I could: Persist the string in a field and then map back to enum on data retrieval. Persist this as an integer and then map back to enum on data retrieval. Create separate table for enum value and do a join on data retrieval. Thoughts...
I am writing an application in Erlang/OTP and want to use sequential counters on a version recording system. I first implemented them with mnesia:dirty_update_counter but the experience of using it drove out these hard requirements: The counters must have the following properties: be strictly sequential - 1 followed by 2 followed by ...
My database designs have previously been rather linear, so I'm getting stumped on what is probably a very easy to solve problem. I have a table of "POSTS", which contain posts which may be a child of either a "CATEGORY" or a "TOPIC". What would be the best way to define the foreign key(s) for the "POSTS" table? I suppose I could have ...
I've found several examples of an inventory database. But I'm looking for someting a little bit different. I'm working with SQL. I need to keep track of tooling. Employees can check out tooling and the inventory for that tool will be reduced and a that transaction will be recorded in a (checked_out) table. Easy to far. When the employe...