CODEWITHSUNDEEP
mysqldatabaseorm
Michael Noyb
Michael Noyb

Reputation: 280

simple DB design question

This is probably a very stupid question, but I am just not sure which solution is the most elegant and the best(most performant) way to go in the following scenario.

I have the following tables:

Customer, Company, Meter, Reading

all of the tables above the line are supposed to be linked to one or more records of a "Comment" table. Which is the best way to model this relationship?

I am seeing two solutions here:

1.) use m:n relationships: CustomerComment, CompanyComment, etc. -> easy to extend later on, but a lot of new tables 2.) use 1:n relationships: Comment table has a field for the PK of the tables above (Customer_id, Company_id, ...) -> minimal table approach, but "harder" to extend since I would have to add a new field to the comment table whenever there is a new table that needs to be have comments

The target is a modular application, which may or may not have any of those four tables.

Which one is the better one - or are there more?

Thanks!

Upvotes: 0

Views: 124

Answers (3)

Andrew T Finnell
Andrew T Finnell

Reputation: 13628

This is the problem with using integers for primary keys. You have a few solutions you can use.

The true unique ID for any given row for Customer, Company, Meter, Reading is a UUID. Maybe because of the database design the primary key has to be an integer but that is ok. This means you never have to add fields to the COMMENTS table if you have a new type in your system. It will always reference by the types ID.

Your tables can look like this:

CUSTOMER
   ID UUID

COMPANY
   ID UUID

METER
   ID UUID

COMMENTS
   ID
   RELATED_TO UUID
   COMMENT TEXT

Now you can attach comments to any table that has a unique ID.

If you want to support referential constraints

OBJECT is a table that holds all of the ID's of all the pieces of data you have in your system. We really start building a system in which you can associate any comment with anything you want. This may not be suitable in your design.

OBJECT
   ID UUID 

CUSTOMER
   ID UUID
   FOREIGN_KEY (ID) REFERENCES OBJECT(ID) ON DELETE CASCADE

COMPANY
   ID UUID
   FOREIGN_KEY (ID) REFERENCES OBJECT(ID) ON DELETE CASCADE

METER
   ID UUID
   FOREIGN_KEY (ID) REFERENCES OBJECT(ID) ON DELETE CASCADE

COMMENTS
   ID
   RELATED_TO UUID
   COMMENT TEXT
   FOREIGN_KEY (RELATED_TO) REFERENCES OBJECT(ID) ON DELETE CASCADE

This complicates the design in order to assure you don't need to add 2 tables for each new type in the system. Each design has sacrifices. In this one you've complicated things by saying for every each entry whether it be Company, Customer, Meter I need an associated ID int he Object table so I can put a foreign key on it.

Upvotes: 2

Kevin Burton
Kevin Burton

Reputation: 11936

I would use seperate tables, that way you can keep the referential constraints simple.

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

I prefer one for each pair - CustomerComment, CompanyComment, etc. It eventually will speed up your queries, and while it isn't as 'extensible' as a single CommentLink table, you'll need to make schema changes when you add something else that needs comments anyway.

Upvotes: 1

Related Questions