How to start the DataBase design for a web project?
Hi, I plan to develop a web directory system.(php used) But i don't know how to start the Data Base design. Are there any good books/tutorials about it? Thank you. ...
database-design
MySQL table design: Primary Foreign Multi-Column Key possible?
I am currently thinking about the database design of a 'friends' table (many-to-many, stores the friendships between users). Columns: user_id friend_id Does it make sense to prefer a multi-column primary key instead of an additional 'friendship_id' column? If so, can I add Foreign Keys for both columns? ...
How to store multiple items in database. Confused with database structure
I had a select box where the user will select the list of cities they travelled. After that the admin will search for the users with particular cities. I am very confused with the structure of the database. There are totally 300 cities. Creating column for each city and store it with boolean on or off, looks a childish technique. Any o...
MySQL database orginization and PHP select
I am setting up a MySQL database and will be querying it with PHP. Is is better to break the information up into multiple tables and do select queries on each of the tables; or is it better to put all the items in one table and do a single select query? For example: It will hold information on upto 7 cars; which would mean that their ...
Datastore Design Inquiry
I'm creating a Trivia app, and need some help designing my model relationships. This question may get fairly complicated, but I'll try to be concise. Trivia questions will all be part of a particular category. Categories may be a category within another category. If a trivia question is created/removed, I need to make sure that I als...
Limit a sqlite Table's Maximum Number of Rows
I am looking to implement a sort of 'activity log' table where actions a user does are stored in a sqlite table and then presented to the user so that they can see the latest activity they have done. However, naturally, I don't feel it is necessary to keep every single bit of history, so I am wondering if there is a way to configure the ...
Is it highly necessary to record the registration date of new website users?
What are the advantages and disadvantages? ...
Appropriateness of a Django Textfield in a Model
Hello everyone, I have a field in a model that I want users to feel like they can write an arbitrary amount of text in. Django provides a CharField and a TextField in the models. I assume that the difference is that one of them is a char(max_length) and the other is a varchar internally. I am tempted to use the TextField, but since it ...
What is a good way to implement tags on a posts like SO
I want a tag system like whats on SO but Im not sure how to implement this in the database. This is how i was going to implement it. Here are my columns in the database Article Table ID Title TitleSlug Date UserID Description IsDeleted TagID1 TagID2 TagID3 TagID4 TagID5 TagName1 TagName2 TagName3 TagName4 TagName5 Tag table TagID...
SQL mapping between multiple tables
This is a SQL design question. First, the setup. I have three tables: A, which is automatically populated based on a query against a linked server. The data in this table cannot be changed; B, which has just a dozen or so rows, containing the names for collections of A's; AtoB, which is the mapping table by which A's are organized into...
Special care/settings for databases that hit a few tables in particular
I suspect that lots of business related software is designed to manage a particular set of information; customers, orders, leads or products and/or a combination. This has been the case in most of my experience. All other tables are usually data supporting the major ones. If this isn't the case, then assume it is for a moment :) because...
Hierarchical filtering in a relational database
I have a bunch of items in my program that all belong to a specific category. I'd like to return only the items that belong to that category. The problem is that categories can have parent categories. For example, let's say there's a category "Stuff" with the child category "Food" with the child category "Fruit". I have the items, Ap...
Designing a Localized Database Schema
I'm working on a web application that I plan to make available in multiple languages. As I design the database, I'm going back and forth between two different ways to store localized descriptions and whatnot in the database. First option is the well-known table_name, table_name_ml type option: TABLE Category ( ID int, ParentID i...
CakePHP: Model that belongs to multiple others, one at a time
I have three tables: "users", "courses" and "documents". I would like to upload documents to users as well as to courses, so I would need two belongsTo-relations for the Document model. Some belong to one model, some to the other. Is there a simple solution to construct these relations? How could I set up the "add"-actions? I know I c...
Data structure for dynamic dropdowns that cascade
Ok, I'll probably be down-voted on this question but my heads not working and I can't come up with a neat solution. Basically, I need to hold a series of dropdownlists in a database or xml file which will eventually be displayed and used on a page. Problem is I also need them to cascade and hold state after a form submission in MVC. Ha...
Database Design For Multiple Product Types
Lets say I have a database containing four different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything. What is the best ...
Design for simple database for an event stream
For a personal project, I want to record a stream of unrelated events, to gather simple statistics on later. Each event can have [wildly] different data associated with it. I'm looking for some advice on how to structure this. As this is a small project, my main goals are: Ease of setup Ease of use (i.e. no strange joins) Performanc...
Practical design for table field referencing more many tables.
The challenge I am facing is trying to come up with a good model design for a common table. A table "notes" is to be referenced by different objects. One way, I thought is to define 2 fields object type, object Id to record the type and the object the note belongs to. Then I am not sure this is the best practice. When it comes to generat...
Is a varchar 2 more efficient than a varchar 255?
I'm using Django and setting my CharField(max_length=255), even though I only intend to use about 5 characters. Is this less efficient? I've read that it doesn't really matter with varchar but then read that it'll save hard drive space to specify only what you need. ...
Database design: why use an autoincremental field as primary key?
Hi, here is my question: why should I use autoincremental fields as primary keys on my tables instead of something like UUID values? What are the main advantages of one over another? What are the problems and strengths of them? ...