Composite primary key or not?
I personally prefer your 2nd approach (and would use it almost 100% of the time) – introduce a surrogate ID field. Why? makes life a lot easier for any tables referencing your table – the JOIN conditions are much simpler with just a single ID column (rather than 2, 3, or even more columns that … Read more
Hash Collision – what are the chances? [closed]
If you assume that SHA-1 does a good job, you can conclude that there’s a 1 in 2^160 chance that two given messages have the same hash (since SHA-1 produces a 160-bit hash). 2^160 is a ridiculously large number. It’s roughly 10^48. Even if you have a million entries in your database, that’s still a … Read more
Database EAV Pros/Cons and Alternatives
This is not to be considered an exhaustive answer, but just a few points on the topic. Since the question is also tagged with the [sql] tag, let me say that, in general, relational databases aren’t particularly suitable for storing data using the EAV model. You can still design an EAV model in SQL, but … Read more
Facebook like notifications tracking (DB Design)
I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well. We have 2 tables notification —————– id (pk) userid notification_type (for complexity like notifications for pictures, videos, apps … Read more
thread messaging system database schema design
Well why don’t you just ask? 🙂 Let me try to pin down my understanding of your requirement. It seems to me that you are looking at a thread being a linear list (not a tree) of messages between two people. I would think that you might want to allow more people in than just … Read more
How do you deal with polymorphism in a database?
Take a look at Martin Fowler’s Patterns of Enterprise Application Architecture: Single Table Inheritance: When mapping to a relational database, we try to minimize the joins that can quickly mount up when processing an inheritance structure in multiple tables. Single Table Inheritance maps all fields of all classes of an inheritance structure into a single … Read more
Best way to store time (hh:mm) in a database
You could store it as an integer of the number of minutes past midnight: eg. 0 = 00:00 60 = 01:00 252 = 04:12 You would however need to write some code to reconstitute the time, but that shouldn’t be tricky.
Nullable Foreign Key bad practice?
No There is nothing wrong with Nullable FKs. This is common when the entity the FK points to is in a (zero or one) to (1 or many) relationship with the primary Key referenced table. An example might be if you had both a Physical address and a Mailing address attribute (column) in a table, … Read more
What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
Assuming you don’t store things like the ‘+’, ‘()’, ‘-‘, spaces and what-have-yous (and why would you, they are presentational concerns which would vary based on local customs and the network distributions anyways), the ITU-T recommendation E.164 for the international telephone network (which most national networks are connected via) specifies that the entire number (including … Read more