Hello, I'm currently in the process of designing the database tables for a customer & website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just because). For example, here are four related tables from the database so far, one ...
What is the best way to create multilanguage database? To create localized table for every table is making design and querying complex, in other case to add column for each language is simple but not dynamic, please help me to understand what is the best choose for enterprise applicationsThanks a lot! ...
I am a programmer and to be honest don't know street address structures of the world, just how in my country is structured :) so which is the best and common database design for storing street addresses? It should be so simple to use, fast to query and dynamic to store all street addresses of the world which is identifying just by one i...
When I asked our DB designers why our Fact table do not have a PK, I was told that there is no set of columns in the table that would uniquely identify a record, even if all the columns were selected. Whenb I suggested that we an an identity column in that case I was told that "I'd just be wasting space and that it wasn't needed." My fe...
Hi all – I'm currently working on a problem that involves querying a tremendous amount of data (billions of rows) and, being somewhat inexperienced with this type of thing, would love some clever advice. The data/problem looks like this: Each table has 2-5 key columns and 1 value column. Every row has a unique combination of keys. I ...
Is using one database column for each attribute in an ER-model considered best practice? I do maintenance on legacy code, and I see a lot of multiple attributes, as well as business logic squeezed into single database columns, and I wonder if there is ever any good reason for doing so. Example is using prefixes for integer values, say ...
I realize this question is very likely to have been asked before, but I've searched around a little among questions on StackOverflow, and I didn't really find an answer to mine, so here goes. If you find a duplicate, please link to it. For some reason I prefer to use Guids (uniqueidentifier in MsSql) for my primary key fields, but I rea...
Suppose it is a long article (say 100,000 words), and I need to write a PHP file to display page 1, 2, or page 38 of the article, by display.php?page=38 but the number of words for each page can change over time (for example, right now if it is 500 words per page, but next month, we can change it to 300 words per page easily). What i...
Hello, I need to make the databse structure for a real estate website where users can create properties of many types and with many features related to the property. The main categories will be: 1. House (subtype apartment, house, loft) 2. Commercial (subtype: hotels, buildings, offices, factory) 3. Terrains (subtype: urban, agricola, ...
DB Table: id int(6) message char(5) I have to add a record (message) to the DB table. In case of duplicate message(this message already exists with different id) I want to delete (or inactivate somehow) the both of the messages and get their ID's in reply. Is it possible to perform with only one query? Any performance tips ?.....
I am writing a Django application that has a model for People, and I have hit a snag. I am assigning Role objects to people using a Many-To-Many relationship - where Roles have a name and a weight. I wish to order my list of people by their heaviest role's weight. If I do People.objects.order_by('-roles__weight'), then I get duplicates w...
What would be the best way to implement a customizable (meaning, a tree structure with an unknown number of level) tree data structure in a database? I've done this once before using a table with a foreign key to itself. What other implementations could you see, and does this implementation make sense? ...
I have an application that needs to support a multilingual interface, five languages to be exact. For the main part of the interface the standard ResourceBundle approach can be used to handle this. However, the database contains numerous tables whose elements contain human readable names, descriptions, abstracts etc. It needs to be pos...
Best way to store comments flags in a sql db? The comments are posted on a page (mediaId), each comment can have children, comments can be hidden by the person who owns the page or by a moderator. Anyone can flag a comment, but only one category and once. Once a comment has X amount of flags it comes to the moderator attention. If the m...
Hello all, In a classic relational database, I have the following table: CREATE TABLE Person( Id int IDENTITY(1,1) NOT NULL PRIMARY KEY, MotherId int NOT NULL REFERENCES Person(Id), FatherId int NOT NULL REFERENCES Person(Id), FirstName nvarchar(255)) I am trying to convert this table into a Google App Engine table. ...
So I’ve been working on this project at work where I’m coding a php website that interacts with a database I have no control over. The database was “designed” by a co-worker that has been with the company many more years then I have; so in the end decisions are left for them to decide. When I was first pulled aboard on this project I w...
Hello everyone, I need to store entries of the schema like (ID:int, description:varchar, updatetime:DateTime). ID is unique primary key. The usage scenario is, I will frequently insert new entries, frequently query entries by ID and less frequently remove expired entries (by updatetime field, using another SQL Job run daily to avoid dat...
I have a web application running over a MySQL database (in development). I'm considering to migrate my application to Google App Engine, and would like to better understand how my simple relational database model can be transformed to the non-relational approach. I'm a long time relational database person, and I have no experience with ...
How would I model such a situation? How should the database be designed? What classes should I have? Problem Statement: Each employee belongs at least to one project, each project has many tasks, each task is assigned to at least one employee. I should be able to churn out the employees working on a project. the tasks that belong t...
we have a website where the left navigation has lot of links. These links are painted based on a users 'function'. There is a screen where an Admin user can Create a new function and assign it to some users. The problem is that once the new function is created, our code has to be changed because inside our left navigation page we have ...