If I had a site where a user can flag another user post and it cannot be undone or changed, do I need to have a primary key? All my selects would be on the post_id and with a where clause to see if the user already flagged it. ...
We have internal application used to publish articles, using SQL Server 2005. For the task I need to save list of tags when a user publishes a post. Should I make a separate table For Tags and update the ID column for a TAG with the id of the post that lists it, using XML column like TABLE_TAGS TAG_NAME varchar, ARTICLE_IDS XML OR ...
We are having a rather long discussion in our company about whether or not to put an autoincrement key on EVERY table in our database. I can understand putting one on tables that would have a FK reference to, but I kind-of dislike putting such keys on each and every one of our tables, even though the keys would never be used. Please he...
I guess it's a good practice to capture auditable fields to track what happened to a particular entity (say createdBy, creationDate, modifiedBy, modifiedDate) I am assuming if an object is never modified it makes sense just to capture the following auditable fields for an SNMPv3 event (say createdBy, creationDate) I am assuming if an ...
Hi guys, I'm using the Adjacency List Model to create categories, and it works perfectly. When retrieving articles in a certain category (for example electronics), I would like to also retrieve the articles in the sub categories (for example electronics->cameras, or even electronics->cameras->camera lenses). The way I am doing it now ...
What is the best structure for an Orders table having OrderNumber, ItemNumber and CustID that allows for 1 or more item numbers for each order number? ...
Is there an equivalent to cron for MySQL? I have a PHP script that queries a table based on the month and year, like: SELECT * FROM data_2010_1 What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table. I was wondering if I c...
I am using PostgreSQL. I realize there is Array data type for PostgreSQL. http://www.postgresql.org/docs/8.1/interactive/arrays.html Currently, I need to use database to store measurement result of a semiconductor factory. They are producing semicondutor units. Every semicondutor units can have variable number of measurement paramete...
I'm working on an app which I will be displaying events (sporting events, concerts, etc, etc). I'm trying to come up with a model where I can single out teams playing for sporting events, and bands/artists playing in a concert. My initial stab at is it to have an events table, team table, band/artist table. But I can't figure out the op...
Scenario: The "Invoice" has a reference to the class "User". A user object is delete through the user himself or an administrator, but the invoice object still needs a recepient (the user). The user object could be marked as deleted instead of delete it physically. But I think it is a bad design to use an object, that is marked as delet...
I am storing a list of "Users" in a table. The business logic of the application will have a reference to an object with all the data in this table for the currently logged-in user. And be able to allow the user to perform operations if they have the correct access. I'm wondering what is the best way to store "access levels?" One way I...
This question is geared towards MySQL, since that is what I'm using -- but I think that it's probably the same or similar for almost every major database implementation. How do keys work in a database? By that I mean, when you set a field to 'primary key', 'unique key' or an 'index' -- what do each of these do, and when should I use eac...
Clarified Question: When the OS sends the command to write a sector to disk is it atomic? i.e. Write of new data succeeds fully or old data is left intact should the power fail immediately following the write command. I don't care about what happens in multiple sector writes - torn pages are acceptable. Old Question: Say you have old ...
Hey, So I am new to symfony and am trying to make a is-a relationship between several tables. I have a media table that has a id field that is the primary key. I then except to have 2 or more additional tables that are "subclasses" of this table such as an article or event table. These are subclasses of the media table and I put a media...
In Master-Detail relationships, do Detail-tables contain ID columns? Or, should it? In Northwind database, OrderDetail doesn't have an ID column. ...
I understand the differences between CHAR and VARCHAR, one being fixed-length and the other variable-length, and that if all fields in a row are fixed-length, a table will generally perform better. However, something that is unclear to me is that if I give VARCHAR a length, such as VARCHAR(500), does this retain the row as fixed-length?...
Every time I need to desing a new database I spend quite some time thinking on how I should set up the database schema to keep an audit log of the changes. Some questions have already been asked here about this, but I don't agree that there is a single best approach for all scenarios: Database Design For Revisions Best design for a ch...
Folks, I have a dimension table called DIM_FILE which holds information of the files we received from customers. Each file has detail records which constitutes my FACT table, CUST_DETAIL. In the main process, file is gone through several stages and each stage tags a status to it. Long in a short, I have many-to-many relationship. Any id...
Is there a reason for or against setting some fields as NULL or NOT NULL in a mysql table, apart from primary/foreign key fields? ...
I'm making a trivia webapp that will feature both standalone questions, and 5+ question quizzes. I'm looking for suggestions for designing this model. Should a quiz and its questions be stored in separate tables/objects, with a key to tie them together, or am I better off creating the quiz as a standalone entity, with lists stored for ...