I have a pretty primitive table schema now ( in Postgres ): CREATE TABLE "public"."sandbox_demo" ( "id" int4 DEFAULT nextval('sandbox_demo_id_seq'::regclass) NOT NULL, "source" text DEFAULT NULL NOT NULL, "created" timestamptz(6) DEFAULT NULL NOT NULL, "modified" timestamptz(6) DEFAULT NULL NOT NULL ) However, this tab...
Hi, Imagine I have a table with 3 columns: ID (unique ID) Description (varchar 2000) IsDeleted (boolean) I want to add a unique constraint so that all descriptions are unique. BUT only for active records i.e. IsDelete = false. If a user flags a record as deleted, they can re-add a record with the same description as a deleted record, b...
Here is the full details. The web application creates report from CSV files There are different formats of CSV files User will be able to add new formats or will be able to add new fields. My client want me to create table for each type of the CSV file ... So for each new formats there will be a new table ... I believe this is a bad de...
I need to store about 73,200 records per day consisting of 3 points of data: id, date, and integer. Some members of my team suggest creating tables using month's as the table name (september_2010), while others are suggesting having one table with lots of data in it... Any suggestions on how to deal with this amount of data? Thanks. =...
I am trying to create a group notification system. If I am in a group, then anyone who comment on the group's wall, a notification will send out to every group member. Here is my database design: I have two tables: Notification and NotificationRead. NotificationRead +userId (String) +lastRead (int) - default is 0 Notification ... +time...
Subquestioning SQL - when should you use “with (nolock)” In one local financial institution I was rebuked by their programmers for expressing them my opinion that (their programmers' obsession with) (b)locking issues in their MS SQL Server 2005 database(s) did not make much sense to me. What are the possible issues with possible locki...
In one local financial institution I was rebuked by their programmers for expressing them my opinion that (their programmers' obsession with) (b)locking issues in their MS SQL Server 2005 OLAP (SSAS) database(s) did not make much sense to me. (The OLTP databases are SQL Server, Oracle and non-RDBMS ERP). What are locking issues in OL...
What I need: Suppose you're using MongoDB and you have a collection called users, and each user has a "following" array with user _ids of the people he's following. Then you have another collection statuses, with each status containing the _id of its author. How do you display to a certain user all the statuses added by people he's foll...
Could I get some opinions on this MySQL table structure please? Is the amount of varchar fields bad? Are there better alternatives? The kind of data should be fairly self-explanatory. CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `active` bit(1) NOT NULL DEFAULT b'1', `email` varchar(64) NOT NULL, `passw...
Background I am developing a social web app for poets and writers, allowing them to share their poetry, gather feedback, and communicate with other poets. I have very little formal training in database design, but I have been reading books, SO, and online DB design resources in an attempt to ensure performance and scalability without ov...
Suppose I have Tutors who take online webclasses and create learning packs. Both online webclasses and learning packscan be rated by students and finally a tutor's rating is the simple average of all the ratings on his classes and packs. This is the table architecture of our current Ratings table - CREATE TABLE IF NOT EXISTS `Ratings`...
I'm designing an application involving "pages", which have many "blocks", of which there are several types of blocks. I'm struggling to find a good way to hold this data in the database however. I'd imagine there is a popular way of doing this, here are two I am considering: Approach One Pages table id user created, updated etc. Blo...
I have a need to give my users time-based statistics about different kinds of visitors to their pages, such as how many visitors in the last week / last month / overall, and perhaps where those visitors are coming from. Ideally, the data would be rich enough to allow for expansion/customization/re-aggregation if possible. My question is...
Hi, Would like some advice from this. I got a table where I want to keep track of an object and a list of keys related to the object. Example: OBJECTID ITEMTYPE ITEMKEY -------- -------- ------- 1 1 THE 1 1 BROWN 1 2 APPLE 1 3 ORANGE 2 2 W...
I am designing a forum application in php i want to know How to design a database design for forums? ...
I have a site where people can add their favorite TV series. There is one feature that makes it possible to check off episodes that you have seen. Each episodes that is checked off, creates one record in a DB table (with user_id, show_id and episode_id). This table is now over 600.000 rows and is growing very fast! I have indexes set u...
I'm currently developing a small customer relationship and invoice management system for my client. And I have run into some small issues which I would like do discuss. What is the best practice around orders, customers and products. Should my client be able to delete orders, customers and products? Currently I have designed my databas...
I fear I don't know what I'm doing. 1: I have a table called ticket which has a column called total. When the total is updated I want to keep a record of it (old total, etc), so I decided to remove the total column and create a table called ticket_total with columns ticket_id, total, and datetime (the most recent of course is the "curr...
Is it possible to prevent someone other than those allowed from tampering with the database. I guess I am asking if there is an method other than the database login to hamper people from tampering with the database? I am aware of privileges and how only access to certain parts of the database for certain users. I am looking for something...
I have the following table - Actor, Actresses, Director & Movie. First 2 tables have many to many relationship with movie table. So i have created Celebrity_Movie table which has primary key of all these tables as foreign key. So my question is how can I insert records in Celebrity_Movie table. Because there can be multiple actors, ...