0

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 :)

9
  • Sounds like you're talking about two different tables in the same database, and not about 2(!) separate databases?? Commented Apr 30 at 12:11
  • @πάνταῥεῖ yes, thanks, I edited the question.
    – terauser
    Commented Apr 30 at 12:24
  • 3
    I would suggest looking into Database Normalisation and think about putting your data into 3rd Normal Form (3NF). As you mentioned OO, I'd also point out that such thinking is a trap (not an uncommon one, but certainly a trap) -- Attempting to apply "OO" concepts and relationships to databases will generally only lead you down the wrong path. OO and database design have wildly different objectives, so trying to use one as a starting point for the other nearly always ends in poor design choices. Commented Apr 30 at 12:29
  • I think people need to reread this question before down-voting and voting to close. At its heart, this is a question about the history of changes for building and owner entities. In my opinion, this is narrow enough scope for an answer. I up-voted the question. Commented Apr 30 at 12:33
  • I voted to reopen this question. I already had an answer written only to find out the question was closed. Why does this community punish people with less experience, and why to we vote to close so quickly. Read the question. This is perfectly on topic, in my opinion. Commented Apr 30 at 12:59

2 Answers 2

3

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.

1
  • thanks so much, that is exactly the kind of answer I needed! I can go on myself from here. And, hopefully, someday I'll come back to help others. Cheers
    – terauser
    Commented May 2 at 21:02
-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.

10
  • 1
    I didn't vote this answer down, but I can understand why someone did. I'm not sure if this was intentional, but it comes off as a little snarky to me. Commented Apr 30 at 12:37
  • 1
    @GregBurghardt, it makes a serious point, that there is a need for both business analysis, and some experience of administration amongst the staff involved. If the OP spent 7 days actually working in the business and using Excel spreadsheets to store data, he'd be in much better stead than trying to work on imagination.
    – Steve
    Commented Apr 30 at 12:46
  • @Steve could you try answering something actually related to Software Engineering? Oh, and thanks for your advice for my employer, I will certainly pass it along ('hey boss, you need to do X and Y. Trust me, I got it from Steve @ Stack Exchange!'). You may try a mental exercise called hypothetical scenarios. It may help you. Imagine the hypothetical scenario that you are new to database design and would like to learn by asking a beginner question in an internet forum. Oh, wait, you were born with the Great Knowledge and are too busy lecturing people...
    – terauser
    Commented Apr 30 at 21:09
  • 1
    @terauser, just to be clear, I was not one of the downvoters or close-voters on your question. You asked what your approach should be to the database design, and I've told my answer, which is that you should consult the experienced administrative staff in the business to discern how they are already storing the relevant data. If however there is no business and you are a novice in software development, then there is no context for you to learn anything, and by definition you have no experience to draw upon, and therefore it would be foolhardy to commission you for this task. (1/2)
    – Steve
    Commented May 1 at 21:11
  • 2
    You seem convinced that I'm somehow talking at cross purposes, but you (and your employer) seem to have a preconception about database design that it is based on asking novices to use their imagination to execute the design. That's not how working software is designed, and it is not how a competent practitioner is reproduced. I don't have a problem with your question at all - your problem seems to be that I've used the information in your question to identify a flaw in your approach to database design, of a kind which you weren't expecting. (2/2)
    – Steve
    Commented May 1 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.