Usually, you’ll have hundreds of tables, and if you don’t want to have a complete mess, you should follow some organizational rules. Databases rarely have a small number of tables. Maybe the most important reason to use it is to simplify life to yourself. Why should you use the naming convention? In this article, I’ll try to use these rules you’ll meet in most cases. While most rules are pretty logical, you could go with some you’ve invited (e.g., you could call a primary key attribute “id”, or “ID”), and that is completely up to you. That part is completely up to you.Īlso, using the naming convention is not the rule, but it’s desired. Of course, you could decide to only set naming convention rules for tables and column names. You’ll apply these rules while naming anything inside the database – tables, columns, primary and foreign keys, stored procedures, functions, views, etc. I’ve already stated it in the intro, but more generally, a naming convention is a set of rules you decide to go with before you start modeling your database. This time we won’t talk about the data itself, but rather about the database objects and the way they were named. We’ll use the same data model we’re using in this series. Today, I’ll try to explain how you should formulate your naming convention and, maybe even more important, why should you do it and what is the overall benefit from using it. It's something like "An event of type R2 was happening at place R1, starting at time R3 and ending at time R4".A naming convention is a set of unwritten rules you should use if you want to increase the readability of the whole data model. Note that I can basically guess the "template" of the resulting set, R1 R2 R3 R4 That's why you have to write nonsensical queries in order to get something useful. And vice versa, if you can express what information you want through combining those "templates", it would almost automatically give you a corresponding query.Īnd the table you have to work with can be associated with no sane "template". don't just combine tables in a particular way, no! They also combine "templates", so you can say what exactly information a query gives you.
and is located at Lime St., 125".Īll relational operations: selection, projection, joins, filtering, etc. Now, each row in this table yields you a true statement about the world when you put its contents into the template: if S would contain a row (1, "Bob & Co.", "Lime St., 125"), then it would tell you that "A supplier with id 1 is called Bob & Co. Each table must have one-statement "template", for example, CREATE TABLE S (id INTEGER, name TEXT, address TEXT) can have template "A supplier with id ID is called NAME and is located at ADDRESS". Such tables also break the very idea of the relational model. Some designers think that having tables with such structure is "flexible" and "improves performance", but it's not so: doing actual work with such tables requires highly non-trivial queries, which most of DBMSs cannot optimize because they were never expected to deal with such atrocities. Unfortunately, it's sorted in a strange way.Īnd I really advise you to persuade someone in charge to re-design the DB. So, you can do transposing/pivoting/whatever it's actually called like this: SELECT I assume the first query is equivalent to this: SELECT id, F1, F2, F3, F4, F5, F6 Anyway, here is one way to do it in this particular case. It ought to be divided into 4 tables (at least). Oh, so you're stuck with a terrible DB design which you aren't allowed to change.
0 kommentar(er)
