I work in an agency that uses many different databases; Informix, Oracle and SQL Server among others. In this era of reducing costs I have started to look for an Open Source tool capable of converting the schemas from those products to Postgresql and Mysql. Though I have in the past used Erwin and various Embarcadero tools, I want to lim...
I have 2 columns: "string" "int" I set both as primary keys for unique combination. Would searching based on the string column be poorer than normalizing further the string column and having 2 int columns instead? ...
I have two table: job with jobid, jobdescription, etc and user with userid, jobid, name, etc. What is the best practice to link two jobid to the same userid in table user ? I can do this with a intermediate table that store the entries of the jobs of the same user but I am thinking that is a more elegant solution. Thank you. ...
Hi, Im having problems retrieving a field from my ms-access database. The table name is TEST and one of the field's name is HD-TEST When i do: SELECT * from TEST where TEST.HD-TEST='H' and i execute the query, ms-access shows me a dialog expecting the parameter HD. Do you know what could be the reason? Thanks a lot. Kind Regards. ...
Hi, I wonder how Object Oriented data modeling is different from Object Relational data modeling? Is it something like the pluses of both object oriented and relational data modeling were clubbed to achieve object relational data modeling? cheers ...
So let's say that I have a database for managing customers. A customer has all the basic properties that one might expect however, there are special kinds of customers called distributors. A distributor doesn't have any special properties in and of itself, except that it can distribute products to certain markets which are stored in a...
Just wondering if there's a preferred method or Best Practice for storing "Updated By" type fields in a database. I tend to see just the name of the user being stored pretty frequently, but I've been designing my tables with the ID of the user who last updated that table. Is there a reason to use one method over the other? I like my m...
In code, it's generally pretty easy to add new classes to provide additional functionality and such. I have a fairly good understanding of refactoring code and what's involved so YAGNI generally makes sense to me. What I'm not as familiar with is working with and updating a relational database once it's deployed. I'm developing a li...
I'm designing a bd-scheme for mysql. My db stores 3 kinds of points: a, b or c and a path is composed by n pair of points: Route = [ (a1 or b1 or c1 ; a2 or b2 or c2), (a2 or b2 or c2 ; a3 or b3 or c3), ...] create table a_points ( point_id serial not null, owner_id bigint unsigned not null, name varchar(20) not null, ...
I develop software that stores a lot of data in one of its database tables (SQL Server version 8, 9 or 10). Let's say, about 100,000 records are inserted into that table per day. This is about 36 million records per year. For fear that I would lose on performance, I decided to create a new table everyday (a table with current date in its...
I am thinking of creating a "wishlist". Each user will be able to purchase wishes in a shop. How do I store the wishlist? I am thinking of creating an user table and a wishlist table. The structure of user table: Columns: id, username, password etc Columns: id, wish, price, quantity etc user id is the primary key for user table and i...
I am looking to do some modeling and would like to have a tool that can capture not only a relational model like ERWIN but also allow us to easily port to LDAP as an option. NOTE: Visio can connect to an existing LDAP server and draw, but does not allow for you to model first and then deploy, unlike its relational capabilities. ...
I'm working on adding user submitted comments to a personal project. Comments have a number of flags for special purposes. Comments are also versioned for moderation purposes so timestamps aren't need on specific fields. These are the options I have so far in the functional design. (list not final) This list will change at some point ...
I have an online game where I record lots of gameplay stats. These stats tables get large very quickly, and I have to be careful because simply recording more stats can cause the game's performance to get pretty bad, once the table gets big enough. My strategy, which isn't a very good one, is to keep the stats tables small. I have an ...
How would I implement a enumeration field in a database that doesn't support enumerations? (i.e. SQLite) The fields need to be easily searchable with "field = ?" so using any type of data serialization is a bad idea. ...
I have two tables in my SQL database: Company: - ID (autoincrement) - name - address - ... Employees: - ID (autoincrement) - Company_id - internal_id - name - lastname The problem is that I would like to have a employee id (internal_id) that is relative to the company they belong to. I got this dilema since I'm really been se...
I've read a few questions on SO (such as this one) in regards to versioning your data within a database. I liked some of the suggestions that were mentioned. I have for the longest time wanted (needed) to revision many of my tables but never got around to it. Being a programmer with only simple database work under my belt I was wonder...
To clarify, Let us say I have users and userstats in my database and I also have information like city, state, county, and region associated with these two tables. In this scenario I can effectively roll up my userstats to city, state, county and region levels easily by a overnight or incremental process. The alternate scenario where I ...
I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples? ...
I am writing a private messaging system for my web app. Just think of it as doing the same thing as sending PMs on your typical social networking website like Facebook or Twitter, or even sending an e-mail through Hotmail. I have come up with the following migration so far: class CreateMessages < ActiveRecord::Migration def self.up ...