Hi I had parent table which has ID and some other columns and a child table which have many values based on this ID(foreign Key). I wanted to create the table with a Primary Key which is sequence and this Parent table ID as Foreign Key but later I found I had one more Foreign Key EMPID which on combination provides uniqueness. Even for r...
I am going to build a PHP web application and have already decided to use Codeigniter + DataMapper (OverZealous edition). I have discovered that DataMapper (OverZealous edition) requires the use of an extra association table even when there is actually just a one-to-many relationship. For example, a country can have many players but a ...
I have the following tables in MySQL server: Companies: - UID (unique) - NAME - other relevant data Offices: - UID (unique) - CompanyID - ExternalID - other data Employees: - UID (unique) - OfficeID - ExternalID - other data In each one of them the UID is unique identifier, created by the database. There are foreign keys to ensure ...
I have following entities: residential listings, commercial listings, lease listing and featured listings which holds featured properties for each user. The most logical seems to leverage supertype/subtype approach and create following tables: CREATE TABLE listings ( id ..., listPrice ..., ... PRIMARY KEY (id) ) CREATE TABLE re...
I'm in the process of creating a social network. It has several entities like news, photo, which can have comments. Since all comments have the same columns and behave the same way, and the only difference is their type — news, or photo, or something else to be added in the future — I decided to create one table for all comments with a c...
I'm creating a kind of wiki site and have a data structure which consists of a parent entity which has a one to many relationship to a child entity. I'm currently storing this in two tables in the database, with a foreign key relationship from the child entity table to the parent entity table. I need to version this data structure in my...
I am looking to insert and update records in a database using functions and logic that are not available in SQL Server or any other RDBMS for that matter. After Googling around a bit this morning, I have come across the concept of Extended Stored Procedures. As far as I can tell, I should be able to compile my desired functionality int...
Foreign Direct Investment (% GDP) 2000 2001 2002 2003 2004 2005 2006 Brazil 5.09 4.05 3.28 1.84 2.74 1.7 1.73 China 3.22 3.36 3.39 2.86 2.84 3.44 2.81 India 0.78 1.15 1.11 0.73 0.83 0.94 2.13 Mexico 2.87 4....
I manage an online catalog. Currently in-house personnel update it manually, and their changes are immediately visible. Now we want to add a verification step: Tom makes a change, Jerry approves it. I see two paths, but neither seems elegant. Keep a second, 'working copy' of the whole database. Keep a second set of 'dirty' tables insi...
Hello, I am currently planning a new system in PHP/MySQL and want to make sure my database can handle the amount of data that I am planning to store. One of the features of my new project is a "messages" feature like Facebook. I want to make sure I create the best possible experience for the end user. The website will eventually handle 1...
Hello, I'm in the process of choosing database for my application. I have been using MySQL for the longest time but for my current application Performance and Scalability is important and I know MySQL has its limitation and I have been hearing a lot about key-value stores, column-based DBs and document-based DBs and others. I have look...
Hey all, I'm learning Doctrine + Symfony and I may have chosen too complex a data model for my own good. Here's an overview: Users create Gizmos. There are 5 Modules to choose from. Users cannot define new ones, only instantiate them. A Gizmo has any number of Instances in any order. An Instance has one Module ID. Instances are config...
Hi everyone! I'm still beginning to get my head around this whole relational-database-thingymawhatsit. Anyway, I'm a PHP programmer so I can do all that shabang. I just need to think what would be the best method for this scenario... I'm developing a site (with CodeIgniter if that's any help) - a site where users can post content (thi...
I have a new table I'm going to add to a bunch of other summarized data, basically to take some of the load off by calculating weekly avgs. My question is whether I would be better off with one model over the other. One model with days of the week as a column with an additional column for price or another model as a series of fields ...
More a general brainstorming question: what is the state-of-the-art in designing a (relational) database which scales to very large amounts of data? And given today's technology trends, how do we expect to design them in 5-10 years? By scalabiliy, I mean in particualar the ability to increase capacity with linear cost by adding hardware...
Just looking for some suggestions on how to approach the database design for this. On my site a user can get points for performing different activities. Currently there are 3 activities for which I award points - but the design has to be scalable where I can add other activities for awarding points as well. So today - the user gets poi...
I'm creating the database for monitoring status of applications' functionalities. The logic is following: Each application has its own, specific list of functionalities that I'm monitoring. Each functionality belongs to only one application. There is a Functionality table that has foreign key to Application Each application runs on one...
I am trying to design an HR leave application system. I need to know how best I can try to implement the following requirement in the front-end, as well as how to persist data in the db so that I can handle validations dynamically. Here is a small example of how we'd go about validating whether the employee is eligible for a leave. Sup...
Hello, I have a payment_types table in which you can enter different payment types such as direct debit, credit card, credit card on file, etc. for an event. For each payment type, you can specify whether to allow user to pay by installment and what the start and end date and number of installments it should allow. Should I have a sep...
I'd like the ability to create a schema for multiple database types such as MySQL, SQL Server, and PostgreSQL. I know ORM tools such as Hibernate can do this, but I won't be using an ORM to access the database so would like a solution that doesn't depend on one. Are there any tools that can do this? Edit: I forgot to mention that this ...