table-design

Database design question. BIT column for deletions

Hi guys, Part of my table design is to include a IsDeleted BIT column that is set to 1 whenever a user deletes a record. Therefore all SELECTS are inevitable accompanied by a WHERE IsDeleted = 0 condition. I read in a previous question (I cannot for the love of God re-find that post and reference it) that this might not be the best des...

Database Design Question

Hi, I am designing a database for a project. I have a table that has 10 columns, most of them are used whenever the table is accessed, and I need to add 3 more columns; View Count Thumbs Up (count) Thumbs Down (Count) which will be used on %90 of the queries when the table is accessed. So, my question is that whether it is better to ...

Track user's country in PHP & MySQL

Hi, I'm creating a URL Shortening website. I want to allow users to view the stats for the links. I planned to do Country stats. I think I should use a library similar to GeoIP for PHP to get the country, but, how can be the MySQL. My weakness is the design of the MySQL tables. Can you recommend me a table format, please? ...

In database table design, how does "Virtual Goods" affect table design -- should we create an instance of a virtual good?

When we design a database table for a DVD rental company, we actually have a movie, which is an abstract idea, and a physical DVD, so for each rental, we have a many-to-many table with fields such as: TransactionID UserID DvdID RentedDate RentalDuration AmountPaid but what about with virtual goods? For example, if we le...

In SQL / MySQL, are there reasons not to put one-to-one relationship in the same table?

One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table? ...

How should I design my MYSQL table/s?

I built a really basic php/mysql site for an architect that uses one 'projects' table. The website showcases various projects that he has worked on. Each project contained one piece of text and one series of images. Original projects table (create syntax): CREATE TABLE `projects` ( `project_id` int(11) NOT NULL auto_increment, `pr...

Help with database design

Hey im new to database design and having trouble trying to figure this one out. I have two tables Team and Fixtures. Team has rows of football teams and Fixture has 2 of those football teams in each row (home and away team). I want to link team id to home_team and away_team but it doesnt allow me to. Please tell me how i can solve this. ...

redundant column

Hi there, I have a database that has two tables, these tables look like this codes id | code | member_id 1 | 123 | 2 2 | 234 | 1 3 | 345 | 4 | 456 | 3 members id | code_id | other info 1 | 2 | blabla 2 | 1 | blabla 3 | 4 | blabla the basic idea is that if a code is taken then its member id field is fil...

In RDBMS, is there a formal design principle for Concrete objects, such as Course vs CourseSession?

In designing RDBMS schema, I wonder if there is formal principle of concrete objects: for example, if it is Persons table, then each record is very concrete and unique. Each record in fact represents a unique person. But what about a table such as Courses (as in school). It can have a description, number of units, offered only in Autu...

"There can only be one IDENTITY column per table" - Why?

"There can only be one IDENTITY column per table" Why is it so? Take a scenario of a vehicle, there exists a chasis number which is unique as well as the registration number which turns out to be unique. To depict this scenario in sql server we need a custom implementation for on of the columns. Conversely, in Oracle you can have as man...