Advice for database design and approach

Question

I ask this to learn from experienced DB designers/architects. I need a high-level overview, I'm not interested in the specific tools for now. Rather, I'd like opinions on how you would approach this task and the recommended ways to do this.

I was asked to create a database for a small company that wants to 'manage' its client's buildings. I acknowledge that 'manage' is a rather broad term, but this is not uncommon for clients, you know. So bear with me for now. Also, I am a novice in this particular task so I am learning.

At first, I imagined two related tables, one for the owners and another for the buildings. The Owners table would have columns such as Owner ID, name, surname, amount of buildings, tax codes, history of membership, etc. The Buildings table would have the information of each building, such as Building ID, owner ID, address, surface area, estimated value, tax status, maintenance status, maintenance history, etc. The amount of columns/fields, and exactly which columns are needed is not really the point for now. The important thing is that

• both tables are related (a given owner can have multiple buildings, and each building can have multiple owners), and
• the information include both 'native' types (strings, dates, numbers) as well as history-like. By this latter I mean, for example if we need to store the history of recorded values of a building along the years, that field requires a list of values (say, year, value and a 3rd field with 'notes'). This history is a feature that belongs to the building, but I am not sure how to include it. Should I consider the value history as a separate table for each building? Also each building needs several 'historical' data that we will need to plot, eg. the taxes paid each year for the last 10 years (don't ask me about the usefulness of this, I have no idea). Should I consider each 'history' (taxes, value, maintenance cost, etc.) as a separate table related to each building? This could be nice because it seems more 'modular' to me, so more tables can be added later with more historical values, while adding just one additional field to the 'buildings' table. Make sense?

Similarly, each owner will also have historical values (eg membership, number of buildings owned, etc.). So how to deal with this?

After thinking about all this, it also comes to my mind that maybe it is useful to think of these as classes (in the OOP sense), so each building is an object with many fields (attributes) and I can even assign methods. Then it becomes much clearer to me.

Am I mixing things here? What is the initial approach you suggest, can you point me to some sources to read about this? I don't even know the appropriate name, I assume it is related to 'database design' or 'data engineer/architect'.

Any comment will be of much help :)

Answer 1

At first, I imagined two related tables, one for the owners and another for the buildings. The Owners table would have columns such as Owner ID, name, surname, amount of buildings, tax codes, history of membership, etc.

Two tables aren't going to be enough to represent all of that properly.

After thinking about all this, it also comes to my mind that maybe it is useful to think of these as classes (in the OOP sense), so each building is an object with many fields (attributes) and I can even assign methods. Then it becomes much clearer to me.

It's fine to think about it that way for a frame-of-reference, but if you go too far with that analogy, you might run into issues with your DB design. A normalized database model can be isomorphic to an OO-model if you OO model is made up only of simple classes where all the fields are simple types (string, int, etc. but no lists or other containers) and references to parent objects. Adding functions to tables isn't something a standard DBMS supports (to my knowledge,) and I would avoid creating anything like functions and stored procedures in the DB unless you really know what you are doing.

The most common error that I see beginners make are often around trying to use columns to represent lists. In other words, if you have columns foo1, foo2, foo3, ... foo10, you are on the wrong track. You should create a foo table which has references back to the parent table. Another common issue is to put the same data in many rows of a table. Your tax code info might be like that. If the tax code is just identifier, it might be OK to keep that in the building table. But if there's more columns associated with that tax code that you need to track, you probably want a tax code table instead of copying that tax information into every row that references the same tax code.

I would recommend learning code principles of relational databases such as normal forms (at least the first 3.) Do yourself a favor and learn about surrogate keys. Also, try to avoid using null values in data if possible.

Answer 2

What is the initial approach you suggest

If your employer already actually has a business operation, then I would suggest consulting the staff currently responsible for its administration, to discover how they store information.

And if your employer doesn't already have a business operation, then they would do well to hire an experienced office manager and work out the clerical needs on paper for a while as the business grows, rather than proceeding immediately to a software implementation via a novice analysing an imaginary business.