database-design

Many to Many Relation Design - Intersection Table Design

I'm wondering what a better design is for the intersection table for a many-to-many relationship. The two approaches I am considering are: CREATE TABLE SomeIntersection ( IntersectionId UNIQUEIDENTIFIER PRIMARY KEY, TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL, TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NUL...

Implementing a voting system without requiring registration

Hey everyone, I'd like to implement a voting system on my site, without having to force them to create an account. They would ultimately be voting up/down a piece of content which has a unique ID. I know that I could store entries in a table with IP/ID, but what if there are more than one user coming from the same IP? Is there a way t...

Differences between set and ordered_set in Mnesia?

What are the differences between a table of type set, and a table of type ordered_set? I'm interested in the differences in read/write performance, what the ordering is based on, the effects across distributed nodes, and such. ...

In a site like StackOverflow should the Question and its Votes be separate tables?

I'm making a site like StackOverflow in Rails but I'm not sure if it's necessary for the Votes on a question to be stored in a separate table in the database. Is there any good reason to separate the data? Or could I store the Votes as a single sum in a field of the Questions table? ...

Efficient week statistics for a QuerySet

I am working on an open source Django time tracking app, Djime, and I'm trying to come up with a more efficient way to produce statistics. So far, we've had some rather long-winded procedural code that would get all TimeSlices for a period, and collate them together in a huge nested list/dictionary mess. What I'd like to do is to set up...

In a StackOverflow clone, is it acceptable for Questions and Answers to be separate tables?

Based on the StackOverflow data dump, it seems that S.O. represents questions and answers as a single table - Posts. However, a question has a title, a body and tags associated with while an answer only has a body. To me, at least, this indicates that they are distinct enough that they should be separate tables. Besides, I don't like...

How to effectively do database as-of queries?

Excuse the long question! We have two database tables, e.g. Car and Wheel. They are related in that a wheel belongs to a car and a car has multiple wheels. The wheels, however, can be changed without affecting the "version" of the car. The car's record can be updated (e.g. paint job) without affecting the version of the wheels (i.e. ...

BALD-D battle Against Bad Database Design

I'm no DBA, but I respect database theory. Isn't adding columns like isDeleted and sequenceOrder bad database practice? ...

What is the industry standard database modeling language?

Before anyone votes to close this as a dupe, know that every question I found so far was a question about specific programs that are good for database modeling. My question is what is the industry standard language (if there is one) for modeling a relational database? I know UML is very popular in general, especially for OOP modeling, ...

In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

In an application similar to StackOverflow that I am building, I am trying to decide what relationship my Questions, Answers and Comments tables should have. I could have Questions and Answers both be represented by a single table Posts. That would allow Comments to have a single foreign key to Posts. But if Questions and Answers are ...

Identity column maximum value in SQLite DBs

I have a purely academic question about SQLite databases. I am using SQLite.net to use a database in my WinForm project, and as I was setting up a new table, I got to thinking about the maximum values of an ID column. I use the IDENTITY for my [ID] column, which according to SQLite.net DataType Mappings, is equivalent to DbType.Int64. ...

linq to sql , model first development ?

Hi, Is is it possible to use l2s in model first approach? Here's the think i want to achieve: I want to write my app in TDD manner, so I want to write some domain specific objects first and then base on that generate database model. One solution is to l2s as DAL but and map linq generated entities to my custom domain objects(I Rob C. in...

Should the descriptive tags associated with an entity be stored in a separate database table?

I have a Questions model, and just like StackOverflow, each question can be tagged with multiple descriptive tags by a user. What I'm trying to decide is whether it's necessary for the Tags associated with a question to be stored in a separate table in the database. Or could I store the Tags as a single field of the Questions table as...

What is a better way to store status updates in a database?

I'm developing a web application that will enable users to post short status updates similar to Twitter. The only way I can think of storing these posts is to have a large "status_updates" table that stores EVERY user's status updates: -------------------------------------- | table: status_updates | ------------------------...

Best way to organize this structure?

I have a database of foods, which I would like to divide into a tree structure of categories, subcategories and sub-subcategories. For example, fruits -> apples -> fuji, or fruits -> apples -> cortland I would like each parent page to show its immediate children (fruits page shows apples, oranges and all other fruit; apples page shows ...

One big table or separate tables to store product reviews of part types?

I need to make 100 or so tables. I have tables called PartStatsXXX and the tables to be made will all be called PartReviewXXX (they pair up with each other in a 1:n relationship). Is it efficient to create one big table to store all product (product and part being the same term from a business perspective) reviews? Someone mentioned mak...

[UML] Mapping a relation between objects in the same table

Hello guys! I've ran into a design issue; in a project, i have three tables: League, Players, and an association table LeagueToPlayers that maps multiple to multiple connections between the first two (as a player can simultaneously play in two or more leagues): League Players LeagueToPlayers Id Name Id Alias ...

Database columns type prefix

I’ve been developing solutions with databases for more than 11 years now, and it seems I’ve “developed” a rather controversial opinion about naming columns in my tables: I always give them a 3 or 4 character type prefix, i.e. intGroupID, nvcTitle, dtmCreated, bitPlayerHater, etc. I’ve worked with several other developers who all absolute...

how to keep a track of last modified time

I have an PARTSORDER table. I want to keep the track of last modification time for easy record for easy access later (query orders which was modified two days ago). Should I have a column with datetime type to put time in it or a better way you usually use? thanks, ...

Store spreadsheet kind of data in SQL Database

I want to store the spreadsheet data in database, so right now i am using key/value pattern to store the same but its taking too much time to retrieve it from data base, if the data is huge. Do anyone has the best way to do the same. How Google saves their spreadsheet data? Usually I have to show 30 to 40 columns and 10,000 rows on the...