We all know that for relational databases it is best practice to use numerical IDs for the primary key. In couchdb the default ID that is generated is a UUID. Is it best to stick with the default, or use an easily memorable identifier that will be used in the application by the user? For example, if you were designing the stackoverflow...
I am using JPA to see how helpful it will be, rather than using straight SQL, and I realized today that one of my columns is incorrect. I have a playlist application. Each playlist has a title and a list of keywords that signify the songs to include. So, I may have this as one row, where there are three keywords. Exercise steady...
In have entities: Collection and Item. Each collection has many items. Both Collection and Item can be edited collaboratively by all users of the application. I have to provide a way to undo changes. Versioning Item records is straightforward, the difficult part is to version the relation between the Collection and Item. The allowed op...
I'm trying to model a site similar to StackOverflow. It has a set of users and questions, and user vote on questions. Each user can only have one vote on each question. What should the structure of my "VotesOnQuestions" table look like: Should I have an auto-generated "VoteID" column? How do I disallow, at the schema level, a user fro...
I'm planning to create a custom system for comments. I was wondering about comment moderation. For approving comments, is it as simple as just creating a field called "Moderated" in MySQL? What's a good suggestion for countering spam? Akismat? ...
Just wondering... why does Google app engine recognize "special" string data types such as Link, Email, PhoneNumber, PostalAddress and such? They seem to be simple text types, and don't even have any helper methods which would, for example, extract host/port information from Link. What design decision would warrant such a distinction? ...
I have four tables: users: id, thread_id threads: id, language_id posts: id, user_id, language_id languages: id USERS.thread_id is a foreign key to THREADS.id, POSTS.user_id is foreign key to USERS.id and POSTS.language_id foreign key to LANGUAGES.id. I can't delete an user because the POSTS.user_id will throw a foreign key constrai...
Just a few days ago I found out about this miracle called CakePHP so I am pretty green to it. I need to build a mail application, so I have followed the convention and created: Database description: Table of users <user_id (primary key), fname, lname>. Table of mails <mail_id(primary key), from (foreign key to user_id), to (foreign ...
I want to populate the contents of a combobox with country names/flags reading from a database table. I want the first item to represent no selection. (The user may not know the country info during first insert) Should I include an item for this in db. Or should I add an item representing the no-select to the array in the code. Which app...
Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits? ...
Obviously both have their benefits. MyISAM is fast but may get currupted easily, InnoDB is slow but is more stable thanks to transactions and foreign keys. So it could be good to mix both engines in one database. If that's possible? ...
I'm in the process of designing the database schema for an application that in the future could involve spreading some tables over several databases due to the number of entries a single user might make. I'm currently designing table relationships keeping common best practices in mind, but I'm not thinking about server architecture, tabl...
My Schema looks like this: CREATE TABLE items ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT NOT NULL, quantity INTEGER NOT NULL price TEXT NOT NULL, category_id INTEGER NOT NULL ); CREATE INDEX "item_id" ON "items" ("id"); I'm setting price as TEXT but it probably should ...
I've shown up at a new job and discovered database which is in dire need of some help. There are many many things wrong with it, including No foreign keys...anywhere. They're faked by using ints and managing the relationship in code. Practically every field can be NULL, which isn't really true Naming conventions for tables and column...
Hi all developers, How can i store multiple values in one attribute. for example: column name: telephone i want to store 5,7 and 16 this three different values in one column (telephone) in one row. ...
Imagine a meta database with a high degree of normalization. It would blow up this input field if I would attempt to describe it here. But imagine, every relationship through the entire database, through all tables, go through one single table called link. It has got these fields: master_class_id, master_attr_id, master_obj_id, class_id2...
For example, lets say I have an entity called user and an entity called profile_picture. A user may have none or one profile picture. So I thought, I would just create a table called "user" with this fields: user: user_id, profile_picture_id (I left all other attributes like name, email, etc. away, to simplify this) Ok, so if an user ...
Apologies for the less than ideal title; had a hard time coming up with something. Three entities: Products, Clients and Client_Product_Authorization. A Client has access to many Products. A Product is accessed by many Clients. A Client_Product_Authorization authorizes a Client to access a Product. We have 100,000+ Products (virtual...
I'm sure that I missed something, and that I've got something plain wrong. 1:(0-1) (one to zero or one) 1:1 (one to one) 1:(0-n) (one to zero, one or many) (0-m):(0-n) (zero, one or many to zero, one or many) m:n (many to many) m:1 (many to one) m:(0-1) (many to zero or one, i.e. 10 people share one meeting room) Constrained Rela...
Hello Merry Christmas and happy new year to all. I wondered if there is any site out there with some detailed examples of normalization and DB design in general.Any comments are welcome.Thanks for your time. ...