Should conceptually different objects with several similar properties be stored in one or more tables?
Suppose A and B are conceptually different entities that have several similar properties and, of course, their own specific properties. In database design, I have to put these two objects in one large aggregate table or two appropriately designed tables.
For example, I have two types of payment; Online payment and manual payment with the following definition,
TABLE [OnlinePayments]
(
[ID] [uniqueidentifier],
[UserID] [uniqueidentifier],
[TrackingCode] [nvarchar](32),
[ReferingCode] [nvarchar](32),
[BankingAccID] [uniqueidentifier],
[Status] [int],
[Amount] [money],
[Comments] [nvarchar](768),
[CreatedAt] [datetime],
[ShopingCartID] [uniqueidentifier],
)
and
TABLE [ManualPayments]
(
[ID] [uniqueidentifier],
[UserID] [uniqueidentifier],
[BankingAccID] [uniqueidentifier],
[BankingOrgID] [uniqueidentifier],
[BranchName] [nvarchar](64),
[BranchCode] [nvarchar](16),
[Amount] [money],
[SlipNumber] [nvarchar](64),
[SlipImage] [image],
[PaidAt] [datetime],
[Comments] [nvarchar](768),
[CreatedAt] [datetime],
[IsApproved] [bit],
[ApprovedByID] [uniqueidentifier],
)
One of my friends told me that creating two different tables for similar objects like this is not a good design practice and should be put in the same table for performance and data manipulation convenience.
Now I'm wondering what to do? What's the best practice in this case?
a source to share
Your question starts with an interesting word: conceptually. Conceptually - logically - all payments are basically the same. Most of the time, your application is probably just interested in payments - is the customer paid? Has the payment been paid? do we need a refund?
So, conceptually, your application probably needs a PAYMENTS table. But you have two different types of payment and you may have additional types in the future (direct debit, standing orders). You can represent these subtypes in three ways:
- add the PAYMENT_TYPE column and many additional columns to the PAYMENTS table.
- implement separate child tables for different types of payments, keeping only subtype columns.
- what you currently have is undo the PAYMENTS table and duplicate the shared columns in each subtype table.
From experience, I've found that a single table is an absolute nightmare to code. We can get away from it when we only have two subtypes, but the logic gets darker the more subtypes we add. This also means that we cannot refer to subtypes in foreign keys.
The "no shared table" solution is - in my opinion - preferable to a single table. Its main disadvantage is that it makes it difficult to work with the generic object. We cannot force a foreign key to PAYMENTS. It also helps to have a database that supports UID sharing between tables (like an Oracle sequence) and that supports the UNION ALL operator.
Supertype solution plus subtypes is by far the best solution. This makes it easier for us to deal with general PAYMENTS and certain types of payments. We get an intuitive data model, we get the ability to enforce foreign keys with the appropriate granularity, we get what fits our application.
However, you are using LINQ-to-SQL which does not currently support supertype / subtype . I really dislike data models due to the limitations of the ORM tool. Other applications that share the database have to pay the cost of the custom model, even if they don't use the ORM tool in question (and quite often multiple clients have access to the same database).
Anyway....
Keep your current design. It's simpler and cleaner. In terms of performance, while there may be scenarios where one table is faster, there will be other scenarios where individual tables perform better. Additionally, for those cases where you need to handle generic payments, SQL Server supports UNION ALL .
a source to share
I could create three tables - Payments
with common fields and a null foreign key referencing the other two, and two specific ones with foreign keys referencing Payments
. With the right indexes and a good relational engine (like PostgreSQL), I've always found that this hybrid inheritance-like approach (with backpointers) works well. If you are stuck with a lower engine for example. one with an optimizer that can't easily combine multiple indexes into a single search, then I guess a single table approach (holding your nose as you use it ;-) might be helpful. I don't see the two-table compromise buying you over any of these solutions - "clean" three tables or "usability beats clean" one-column; take care of expanding this point?
a source to share
split..split..split..split ...
Since you have ID as a generic tag (and the ID is unique for every transaction):
Have TABLE1 ( ID, USERID, AMOUNT, Comments, CreatedAt, *TYPE* )
i.e. general fields Use the TYPE field to store if it is OnlinePayment or ManualPayment
Store TABLE2 as follows:
TABLE [OnlinePayments] (
[ID] [uniqueidentifier],
[UserID] [uniqueidentifier],
[TrackingCode] [nvarchar](32),
[ReferingCode] [nvarchar](32),
[BankingAccID] [uniqueidentifier],
[Status] [int],
[ShopingCartID] [uniqueidentifier], )
Save TABLE 3 as follows:
TABLE [ManualPayments] (
[ID][uniqueidentifier],
[UserID] [uniqueidentifier],
[BankingAccID] [uniqueidentifier],
[BankingOrgID] [uniqueidentifier],
[BranchName] [nvarchar](64),
[BranchCode] [nvarchar](16),
[SlipNumber] [nvarchar](64),
[SlipImage] [image],
[PaidAt] [datetime],
[IsApproved] [bit],
[ApprovedByID] [uniqueidentifier], )
Use a unique id to match rows between table1-table2 and table1-table3
Thus, memory consumption should be reduced to a minimum. This will speed up your queries. Also, performance should not degrade too much, as in other cases.
Also Plz goes through this:
GOODLUCK !!
a source to share
I agree with the above - 3 tables are the way to go. Performance? It will actually be better!
3 tables will give you the ability to fine tune your queries and indexes. It is better to have 1 index per table than 3 indexes per table - in the case of OLTP.
The perceived "join" cost only applies to the worst database optimizers and does not apply to MSSQL and Oracle unless you go on board.
a source to share