Hi Folks Looking for some good advice on db design for tracking multiple banner impressions. IE I have 5 Banners over x Domains I would like to build data on each banner on how many impressions per day per banner etc. So also be able to do lookups for other date ranges. Would it be best to have a date per day per row or just track ea...
I'm doing a project to manage membership and other kind of payments, but mainly membership so I created a polymorphic schema. any idea, improvement, for some reason I don't fully convinced about the schema. as you will see, the idea of having month, year NULL-ABLE is allow save record of any other payment CREATE TABLE IF NOT EXISTS `o...
In my present Rails application, I am resolving scheduling conflicts by sorting the models by the "created_at" field. However, I realized that when inserting multiple models from a form that allows this, all of the created_at times are exactly the same! This is more a question of best programming practices: Can your application rely on ...
Many of the SaaS web app services have a company based concept. So, each company using the service has its own set of users, files, and other data. How do the web apps usually handle this on the DB side? Do they create a new database for every company (containing the data tables related to that company) ? Or do they have some kind of com...
I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL and pgsql so I need fairly standard SQL. I currently have a comment table comment_id artic...
Hi all, I've got two legacy database tables, layouted in simplified manner like this: MASTER SLAVE ident ident sName sName sNumber sNumber sDesc sValue ----- ------ Java Class 'ScenarioMaster' ...
I've asked this question here, but I don't think I got my point across. Let's say I have the following tables (all PK are IDENTITY fields): People (PersonId (PK), Name, SSN, etc.) Loans (LoanId (PK), Amount, etc.) Borrowers (BorrowerId(PK), PersonId, LoanId) Let's say Mr. Smith got 2 loans on his name, 3 joint loans with his wife,...
I'm working on a website currently that needs a commenting system. As this website is brand new, and the database structure has yet to be set in stone, I would like some suggestions on how to best handle a commenting system such as this: Comments must be able to be placed on anything. Including items in future tables. Comments must b...
A relation table is the common solution to representing a many-to-many (m:n) relationship. In the simplest form, it combines foreign keys referencing the two relating tables to a new composite primary key: A AtoB B ---- ---- ---- *id *Aid *id data *Bid data How should it be indexed to provide opti...
I'm creating a game with points for doing little things, so I have a schema as such: create table points ( id int, points int, reason varchar(10) ) and to get the number of points a user has is trivial: select sum(points) as total from points where id = ? however, performance has become more and more of an issue as the points...
I am planning to build a web application that will require a relatively complex database. There are several tables involved with many relationships, enough to make it very difficult to keep it straight in my head. I need a simple and easy way to summarize the tables and illustrate the main relationships so that it visually makes sense ...
I have a name table with (id,first_name,middle_name,last_name,sex) and an email table with (id_fk,email_add) Infact I will be having similar tables of the second kind, like a phone table (id_fk,phone_no), where id_fk is the foreign key referring to the id in the name table. Is it required or rather is there a good reason to have a prim...
Freeware is a plus. Could you also describe with one sentence why it should be good enough for occasional usage? Edit: You might check this cool link , providing more info about entity relationship diagrams and tools Edit: For the chosen answer couple of tips - click on entity even it looks like circle and type the name and Enter - it...
For our next project, we're considering to switch to UUIDs as primary keys for database tables. The project will run on HSQLDB, Derby, DB2 and Oracle, maybe MySQL. Data will not have to be migrated between databases (often), so the "uniqueness" over many DBs is not so much an issue for us. The main advantage for us currently is that eac...
My requirement is for a data model where a full audit trail is retained for changes to every attribute of every object. Object definitions are also fluid: new attributes can appear or go away over time. This audit trail will live separately from the original databases, so a trigger-based auditing model won't work. In a relational data...
I have some extended classes (utilizing both HMAC and cryptographic tech), and I'm hoping to utilize them for a new product's database from said database's stored procedures (inclusive of time logging, user-event logging, etc.) under SQL Server. Can anyone recommend a resource detailing the best ways to achieve this or optionally advise...
Hi. We are working on a datawarehouse for a bank and have pretty much followed the standard Kimball model of staging tables, a star schema and an ETL to pull the data through the process. Kimball talks about using the staging area for import, cleaning, processing and everything until you are ready to put the data into the star schema. ...
Hi guys, i have a doubt (big). Lets take as example a database for, dunno, a whatever company's orders. Lets say that this company make around 2000 orders per month, so, around 24K order per year, and they dont ant to delete any order, even if 5 years old (hey, this is an example, numbers dont mean anything:) In the meaning of have a ...
Is it preferred to use "Id" as the column name for a primary key or "[TableName]Id" as a naming convention? Table: Account Primary Key: Id -- versus -- Table: Account Primary Key: AccountId It seems to be split about 50% / 50% in the implementations that I've seen. What are the advantages and disadvantages in each approac...
I want to create a business directory project. I want to start two businesses with this project, the first will be customized for restaurants and the second will be customized for toy stores. In terms of customization, each business project will have its own domain name, configuration settings, front end templates and language files. ...