Mysql – What would be the best way to model the simple table

database-designindexMySQL

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?

Or, searching for the right row through a table with only 1 column with those 17 numbers concatenated together as a single bigint?

The numbers when combined are all unique from one another (no row has the same values/value as any other). I'm new to indexes and what not, so I have no idea how I should model my table/server logic.

Best Answer

If we're talking about something where you may want to query on one or more of the attributes my suggestion would be multiple columns, indexed appropriately (for a small, infrequently-changing data set, index all the columns).
This works best for the case where you may want to run queries like
SELECT * FROM table WHERE attribute1=1 AND attribute3=2
since you will have several indexed columns and searches on one or more of them will be relatively efficient.

If we're talking about something where you only ever want to query on the aggregate (all attributes match) a single column will be more efficient in terms of storage, and likely in terms of indexing as well.
This works best for cases where the previous example would always devolve into
SELECT * FROM table WHERE attribute1=1 AND … AND attribute17=17
as rather than having to cross-check across multiple columns it will simply be
SELECT * FROM table WHERE attributeColumn=1234567 -- Rather than having to scan 17 indexes (or examine the contents of 17 columns in every row) you will just have the single index scan.

Related Solutions

The best relationship model for high performance

You'll probably want to keep your transaction table as small as possible, so use an integer column as a foreign key to your customer table.

How to model the Twitter lists functionality

I'll try to explain why they merge all relationships into one table.

Whether a users follows, likes or comments a photo all these activities are somewhat similar they involve a user and photo. Note that the comment also requires some text.

So instead of having 3 tables like your data model they went with one.

I don't like that approach though because it merges data that may is similar in its appearance but differs in meaning. Also the comments require extra data (content) that is empty for likes and follows. Having the type described as a string as in their example is also bad (better make an extra table).

A database model would like this for your problem

User(**UserID**, Name, ....)
Post(**PostID**, UserID[FK]Data, Timestamp, ...)
Follow_Users(**UserID[FK]**, **FollowingUserID[FK]**)
Follow_Posts(**UserID[FK]**, **PostID[FK]**)

However this is a database model and not a Data model. A Data model that is kept in memory within some application will be different because it features pointers to other objects and arrays...

Best Answer

Related Solutions

The best relationship model for high performance

How to model the Twitter lists functionality

Related Question