Tag Info

Hot answers tagged

5

You are suggesting a client-side join: Fetch some data from the database via a query, and then in code you perform further joins via lookup tables loaded from files. This is almost certainly going to be slower, and definitely more complex than just doing the join in the database. Think about it - why should you be able to write you own join-logic to be ...


4

Make an Interest table, and then another table (UserInterest) holding the foreign keys of both User and Interest What you're describing is a many-to-many relationship between User and Interest. If you go through the process of database normalization for your data model you will see that holding a comma-separated list of values fails on two points: Each ...


3

Actually, internally the database is not going to store your string representation of your enum in every record. It is effectively making a lookup table of it's own and translating the stored integer value to string (and back) when you interact with the database... From: http://dev.mysql.com/doc/refman/5.0/en/enum.html If you retrieve an ENUM value in a ...


3

The problem is not space. Because space is cheap. It really takes a good book to explain what is wrong with repeating texts in a column, but it's a bad idea. You will get it when you read about database normalization. Database normalization is what strong typing is for programming languages. You can live without, especially for very small projects, but ...


3

It is no problem to have data "not consistent across all rows" in a relational database. This is precisely what relational databases are designed for! You create a table for the data which is common across all activities, and then you create separate tables for data specific for kind of activities. E.g. a football table with a score column, a cycling table ...


3

Take a look at PostgreSQL. You can store JSON in it in a type-safe way. It supports indexing so you can combine relational data with JSON documents pretty well. It is also fast, there are plenty of benchmarks. Also, if you never used NoSQL before, please read this: Why You Should Never Use MongoDB (don't mind the title, article is pretty constructive) As ...


2

I would suggest the following structure due to my experiences in some other applications. First of all I would build a language table: language_id (PK) iso_country_code iso_language_code codepage translation_id (FK) The language_id would represent the primary key. Each available language is added to this table. iso_country_code can contain the ISO ...


2

Advantage: GUID is unique so in the long run if you run into this scenario where you can join using just Guid rather than PK+other field. ex:there sales, order, adjustment, and there is stock, instead of join using PK and transaction type, you can just join using Guid since it 99.9...% guaranteed unique. By using GUID you can generate GUID from code so it ...


2

What you have is not flexible enough. The basic requires are you need to be able to express e.g. the following: U.S. Memorial Day is the last Monday of May. U.S. Thanksgiving is the fourth Thursday of November. Christmas is the 25th day of December. Easter is... maybe just scrape Wikipedia? One simple way to express this is to add fields for the ...


2

Joins are not slow. They are incredible fast if you join to a primary key or indexed column. You should not make design decision from the assumption that joining is a problem. Now, there may be particular cases e.g. with very large datasets or distributed databases, where joining may be a performance problem, and there are various ways to mitigate that ...


2

Any thing ("Entity") that can exist on its own, independently of anything else, should have its own table. User: id, name, hashed_password, join_date, birth_date Group: id, name Relationships between things require generally require "linking" tables. Post: id, user_id, group_id, post_date, post_title, post_content The key to success is proper ...


2

Don't delete records from a database to make them invisible to others. Instead, use an "assigned to user ID" field, and filter the lists by user ID, using a WHERE clause. You also need a Status field. You can remove a record from someone's list by assigning it a status of "Completed."


1

I think you are confusing a few different things here. 1: database normalisation. The question answer you link to is about normalizing out repeated data into a second table with a FK. The key needent be an int and will be repeated, so if your data is JUST the key you are already normalized. 2: ints as keys (due to space?) You suggest that using an int ...


1

Enums are not only faster to insert, delete, update and read. They also take up less disk space, and most importantly: they ensure that you can only store a limited set of values. If you create a field, and you only want to allow values "male" or "female", then an enum is handy to make sure that nobody stores "man". Text fields are just a bad solution to ...


1

It is not unusual to have several keys in a table and there's nothing fundamentally wrong with that. If a table has more than one key then the choice of "primary" key is unimportant - or only as significant as you want to make it. Your boss is correct that it is usually a good idea for every SQL Server table to be clustered. Guids don't make for good ...


1

Well as already stated in the comments of the other question, this is nonsense if you won't query using the id at all. If you use just the GUIDs then leave out the clustered index and just setup the no clustered index on the GUIDs. But never use the GUIDs as clustered index unless you want to have a stress test on your I/O subsystem. ;-)


1

There is no way you can get around having more than one way of uniquely identifying your entities. The best you can do is grit your teeth and confine the problem to the smallest possible scope. For instance: assign the real IDs on the server only. The server should never even be aware that the client uses another ID scheme, only that it delivers new ...


1

Premature generalization? Have you ever read about The Rule of Three? I think you are trying to generalize a little too soon. It will all be based around either the coach or player, the full complete differences between sports organizations I wouldn't know at this point, but I know there are differences from people who work in them, for example ...



Only top voted, non community-wiki answers of a minimum length are eligible