CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
blacktie24
blacktie24

Reputation: 5075

When should a DB table be split into two separate ones?

I have a users table, which employs single table inheritance, so that I can have both Consultants, Clients, and Internal Users all within the same table. I want to create a table for "notes", so that internal users can make notes on clients and consultants. I'm not sure if I should create a consultants_notes and a clients_notes table, or just create a single notes table? What factors should I consider?

Upvotes: 0

Views: 340

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You said in a comment:

I'm not really sure yet how much the notes for each user type will differ, so I'd like to be able to keep it flexible, if possible

I think both ways (one or two tables) will be flexible enough. However, you should also consider normalization. If you then realize that a consultant requires the Date of the note and that the clients do not require that field, then you'll start getting lot's of null values on your table (space, as if a bit costs that much these days :P and lack of normalization).

So it might seem that a one table solution is a disadvantage. However, the one table solution will simplify your queries (readability) and also give you better performance (speed) because you won't have to make a join per each note table.

The advantages and disadvantages of the two table solution are actually the opposite to the ones of the one table. So, what are your main restrictions? If it is speed, you'll go for the one table solution. In my opinion I would go for the two table solution, because I can't stand having unnecesary values in a table. Besides, having a strict design, i.e. one that won't allow you to add a client note value to a consultant note value, is much safer than one that allow you to do that with a simple UPDATE. Although you can add restrictions to column values to come around this, it isn't the same.

Well, that's my humble opinion. Hope it helps, or at least makes you think twice before choosing your design :)

PS: The factors to consider are in bold

Upvotes: 1

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

I would start with one table:

  • You already have just one table for any kind of user.
  • There is no point in making three identical artifacts (and triple the code to use 3 tables).

However if each kind of note will differ a lot from the others you can consider which is better for you: * More tables with specific columns for each kind of note. * One universal table with some extra columns not always used.

Consider:

  • How many notes will be stored in the table.
  • What data will be stored for each kind of note.

Check how to build indexes to access specific kinds of notes and decide what is better for you.

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272086

You have one table for three types of users so why have three tables for their notes?

The problem with three table for notes is that that you'll have to selectively look into one of these tables when you search for notes of a particular user. You'll end up hard coding this logic in your PHP code using if-else statements - or - you'll left join with three tables in your queries.

And what if you decide to have four types of users.

Upvotes: 2

Erik
Erik

Reputation: 222

I see you tagged php so unless you use two different pages to read/write notes for different user types I'd go for a single table because you don't need two separate queries. Unless you expect to have thousands of notes in the table in which case seperate tables might be more efficient.

Upvotes: 0

Related Questions