Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    May 2009
    Posts
    41

    Should Dates/events be separated from Membership table?

    I have a membership table that has a dozen or so date fields, such as join date, resignation date, first performance date, etc.

    Would it be a good idea to separate this into a m:n?

    MEMBER ---> MEMBER_EVENT <--- EVENT

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Two things could lead me down the one-to-many path for that kind of problem. The practical reason for going there would be because you don't know how many events there might be, possibly because the users will want to add event types over time. The purist reason is that it would eliminate NULL values and allow your schema to be certified at higher levels of normalization.

    The practical issue is important to me, the purist appeals to the academic in me, but isn't too important.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Pat, You consider normalization not to be of practical importance? If you're serious then I think it would be instructive to justify that position rather than just dismiss normalization as "purist".

    Quote Originally Posted by Pat Phelan
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.
    I keep wondering what the point of your sig is. Anyone who really believes that theory and practice are unrelated is denying the evidence of several millennia of science and rational thought. Such people would do well to avoid a science-based profession like IT where theory is used every day to solve practical problems. I don't think you are that deluded but perhaps not everyone will read your signature as just mischievous irony.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The difference between normal forms is interesting to academics, but to the "guy in the trenches" trying to solve problems the normalization form is only relevant as it applies to their immediate problem. While the differences between a schema in third normal form and one in fifth normal form are important to academics, those difference are only important to the layman when it impacts their ability to solve everyday problems.

    Normalization is very important to me, to the point that I was willing to "stake my job" on it ten years ago. The reason it was so important was because I inherited a schema that resembled a rat's nest. That lack of normalization had become a crippling problem for the business and for the IT department to solve the problems of the business. Normalization doesn't mean squat to most CEOs or board members... As long as their people can solve their problems, normalization will never cross their minds. Even if normalization is the problem, odds are nearly certain that they'll bring in you or me as a "hired gun" to fix the problem instead of learning about normal forms.

    My sig line is funny, and the irony is plain in my eyes. The problem is that there's a deep truth embedded in those statements that can only be observed by those who actually work in the trenches... Guys who truly know why there need to be at least 13 twists on a corn auger and why you only pour exo-thermic cement mixes when the ambient air temperature will drop below 5 degrees Celcius before full cure. That truth lies outside the experience of academics, they will never perceive or appreciate it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Pat Phelan
    Normalization doesn't mean squat to most CEOs or board members... As long as their people can solve their problems, normalization will never cross their minds. Even if normalization is the problem, odds are nearly certain that they'll bring in you or me as a "hired gun" to fix the problem instead of learning about normal forms.
    Of course. This is a forum for database professionals however and my assumption is that everyone participating here really ought to be interested in solving those kinds of problem.

    Quote Originally Posted by Pat Phelan
    My sig line is funny, and the irony is plain in my eyes.
    Theory is practical. Sadly, as I expect you are aware, there is a shocking level of ignorance and disrespect for genuine knowledge that is very common in the IT industy. Given that state of affairs I think we should always be very cautious about making statements that might seem to cast doubt on the value of science and foundation knowledge - even in jest. Don't feed the animals!

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by _ANDROID
    I have a membership table that has a dozen or so date fields, such as join date, resignation date, first performance date, etc.

    Would it be a good idea to separate this into a m:n?

    MEMBER ---> MEMBER_EVENT <--- EVENT
    One thing to think about is whether these attributes are truly all independent.

    Suppose attributes A and B are optional but where A is supplied then B is also required. If A and B are in separate rows then it may be hard to implement such a constraint. Instead, by putting A and B as (non-nullable) columns in a separate table you can easily enforce the constraint that you cannot have one without the other.

  7. #7
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Pat

    The problem I have with your signature, is that it prevents you from being taken seriously. Basically it is a contradiction, and I am sure you do not mean it that way. We have not gotten to the point of building heavier-than-air flying machines and putting men on the Moon (using 8-bit computers! ) without science, without theory, and the application [practical use of] theory, of science. The goal was the explicit goal of the theory before the practical could be contemplated.

    The practical without the theory is Icarus. Bridges that fall over or buckle in the wind; buildings that cave in under their own weight. Third world palaces built on sand.

    Normalisation does not have to be understood by CEOs and lay people, they are not in the trenches, and they are not seeking answers here. It is important to anyone building multi-user databases, and posting responses in a Database Concepts & Design forum. So it is weird to be asking questions and providing responses to people who are supposed to know what they are doing, and then when the going gets tough, switch tracks and say "it is of no value if a layman can't understand it". We are not lay people; lay people do not understand heavier-than-air flight or Normalisation (and they don't need to). That requires some education, from academics, and practical experience applying the theory, which is a standard part of education.

    Third, there are many sciences that have to be applied in computer systems and databases these days, not just one. It is our job to know all of them that apply; to weigh the competing needs together and to determine a conscious point of compromise. Otherwise (since you have recently posted, you will understand) they go about it with a one-tool mindset and create monstrosities. Recognised and easily avoided by the trained.

    That practical experience, as it matures, provides the ability weigh the competing needs, the major vs minor roles and make sound decisions. That too, is quite different to the black-or-white, all-or-nothing, thinking of the untrained.

    The point is, none of that is possible without training by academics, the understanding of theory, and the application of it. In that order.

    It is one thing to know that one must twist the auger 13 times; it is a different thing altogether, to plant the rows of corn in line with the horizontal contours of the land, and to drive the harvester the same way.

    Anyway, I am not trying to change your mind, just letting you knw, if you do not intend your signature to come across as bitterly against academia and theory, and thus confine yourself to practice absent of sound theory, I would suggest you modulate it a bit.

    ----------

    Normalisation is normalisation, either it is normalised or it isn't; it is not "purist". That term comes into the picture only when people perform haphazard and informal "table design"; when someone else points it out to them, that's what they get called. Correct Normalisation means never having to re-implement a table (and thus never having to change code). Sure, as the database and the functionality grow, they will be columnar expansion, and new tables, and yes, added code, but no need to re-implement existing tables or change existing code.

    Some people are happy with implementing a bunch of spreadsheets linked by row id in a container called a "database"; then spending the rest of their employment "refactoring" them, with all the expense. That is practice absent of theory; and repeated practice does not invent a new theory, no, it is merely habitual bad practice, and a faster method of implementing that habitual bad practice, which needs re-implementing because it was wrong. All that is completely unnecessary if they understood database theory, and had a bit a practical experience applying the theory. Nothing wrong with that, but they may be expert at re-implementing spreadsheets in database containers, they are not expert in implementing databases (once). If implemented according to theory, it will not need re-implementing, but that is lost to them.

    Now, I am not saying that everyone should go out and get a degree in Computer Science, not all of us had the privilege of a tertiary education. But we cannot be blind to the fact that those who are untrained in the theory, will keep producing throw-away designs; and those who honour their teachers will produce designs that do not require re-implementation. There is a reason the market pays more for tertiary qualifications.

    I think that is what people look for, when they come to a Database Concepts & Design forum. How do I do it right, so that I don't have to keep re-doing it; not how do I re-do it faster.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  8. #8
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Android
    I have a membership table that has a dozen or so date fields, such as join date, resignation date, first performance date, etc.
    If you mean "what is the correct database design", then we apply ordinary Normalisation:

    1 If the relation between the MemberPK and the attribute ( any of the one dozen dates, eg. JoinDate) is strictly 1:1 and nothing but 1:1, then it is a column in the Member table.
    Optionality (there may not be a ResignationDate) is fine, that is 1:1 (optional or nullable), or 1:0-1. If there were a ResignationDate, there will be only one.
    2 If the relation between the MemberPK and the attribute (any other of the one dozen dates, eg. RenewalDate or PerformanceDate) is 1:n, that is an event in MemberEvent table.
    In this case, the parent table is a lookup or reference table, is should be called EventType, not Event.
    That is it, end of story. That is the one-fact-in-one-place truth. That is the nature of the universe.

    But, by the sound of it, you are not happy with that, that Normalisation has produced a model with "4 dates in the Member table and 8 dates in the MemberEvent table". Just deal with it. The coding will be easier: when the single date events need to be fetched:
    SELECT SingleDate FROM Member -- only
    when the multiple date events need to be fetched:
    SELECT MultipleDate FROM Member, MemberEvent [WHERE EventType = "xxx"... join ...]

    Needing all the dates to be in one table, or just because you have 8 dates in MemberEvent, you may as well put the 4 single dates there, "it makes the design cleaner", etc, etc. That is rigid black-or-white thinking, and a departure from the truth, a departure from the state of your universe. Feelings, not facts. It is not "denormalised", it is broken, unnormalised.

    BTW FirstPerformanceDate may simply be the first of n PerformanceDates.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  9. #9
    Join Date
    Sep 2009
    Posts
    11
    Quote Originally Posted by dportas
    Theory is practical.
    I must disagree that all theory is practical, but perhaps this only means that we define "theory" differently.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by iambk
    I must disagree that all theory is practical, but perhaps this only means that we define "theory" differently.
    If a theory doesn't work in practice then the theory is proven wrong and the result is new theory which does work in practice. That is Scientific Method.

  11. #11
    Join Date
    Sep 2009
    Posts
    11
    Quote Originally Posted by dportas
    If a theory doesn't work in practice then the theory is proven wrong and the result is new theory which does work in practice. That is Scientific Method.
    There are various meanings: theory: Definition, Synonyms from Answers.com

    Regardless of that, from a "practical" and "real world experience" standpoint we often find we have some obstacles that must be considered for a particular problem. Perhaps it is a simple matter of not having the time to implement the better functional solution to a problem. Many of us strive for 'ideal' but have to make compromises on the way.

    Sometimes there are competing theories that both have acceptance to an extent. Allowing NULL vs not allowing NULL in a database or index might be one example.

  12. #12
    Join Date
    Sep 2009
    Posts
    11
    Quote Originally Posted by dportas
    If a theory doesn't work in practice then the theory is proven wrong and the result is new theory which does work in practice. That is Scientific Method.
    There are various meanings: theory: Definition, Synonyms from Answers.com

    Regardless of that, from a "practical" and "real world experience" standpoint we often find we have some obstacles that must be considered for a particular problem. Perhaps it is a simple matter of not having the time to implement the better functional solution to a problem. Many of us strive for 'ideal' but have to make compromises on the way.

    Sometimes there are competing theories that both have acceptance to an extent. Allowing NULL vs not allowing NULL in a database or index might be one example. This is theory in a more general sense, which I confess is different from something like physics theory.

    Perhaps this issue is that in the computing word, many people treat "good practice" as if it were theory.

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by iambk
    They all boil down to much the same thing: a theory is an explanation of how a system works.

    Quote Originally Posted by iambk
    Regardless of that, from a "practical" and "real world experience" standpoint we often find we have some obstacles that must be considered for a particular problem. Perhaps it is a simple matter of not having the time to implement the better functional solution to a problem. Many of us strive for 'ideal' but have to make compromises on the way.
    A theory doesn't become less real because you don't have time to use it. That would be like saying mathematics doesn't work in practice because you can't do long division! The theory still solves a problem and when a person understands the theory he has a practical tool for analysing and understanding that problem.

    Sometimes there are competing theories that both have acceptance to an extent. Allowing NULL vs not allowing NULL in a database or index might be one example.
    Allowing or not allowing a null is not theory - it's just a decision you take, hopefully based on your understanding of the potential benefits and disadvantages. The ideas that explain those benefits and disadvantages would be the theories and they should be valid whatever decisions you eventually take.

  14. #14
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Discussing theories in the stricter sense would be preferable, because that is how the thread started. If we do not keep it contained, the discussion would range over the exceptions, some people would argue that the exceptions prove the theory false, and then we have to have basic discussions about the Scientific Method, and we would lose sight of the subject. Database Theory.

    Good examples help. The Null issue may not be a good example, but let's stick to that.

    For a person who doesn't understand [a] database theory and [b] the limitations to the application of the database theory, the Null problem and the several methods of solving it, are unknown. They hear the two views, which are decisions or methods, and since they do not understand the issue, the two views may look like two opposing views, two theories. The two options may or may not strike a chord (match other thought or knowlege they do have) and they choose. The implementation they perform has problems; when they hit obstacles, they will not understand the problem for what it is (the Null Problem) they will see it as a problem with their chosen method. The point is, they are not learning about the Null Problem, they are learning the limitation of their choice. Thus, they may re-inforce the choice, or reject it, but for the wrong reasons (limitation without understanding).

    The person who understands [a] and [b], and the Null Problem for what it is, will make decisions re the implementation from a depth of knowledge; when they hit obstacles, they will understand them in a different way; their decision re how to solve the Null Problem will be consistent. (I am not suggesting one method is right and the other is wrong.)

    For the person who has a depth of experience with the application of the two solutions Null Problem, and is dissatisfied with them, they may seek a better method, which will lead them to do research. The database theory does not change, but the language and the vendor offerings are flawed or incomplete. Lo and behold, beyond the two well known solutions, they find there are others. This person is capable of implementing a more serious, more exacting database, where the users are more isolated from the effects of the Null Problem.

    So, from a practical, real world standpoint, you will get three different results depending on which of the three people you use. It has nothing to do with whether theory is sound or not, or that there are two "opposing theories". The theory is unchanged (applied and proven thousands of times). There are several implementation methods, two of which are commonly known, and which appear to be opposing to anyone who does not know the theory, and does not have practical experience with application of the theory.
    Last edited by Derek Asirvadem; 09-24-09 at 03:24.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  15. #15
    Join Date
    Sep 2009
    Posts
    11
    Sorry for such a simple reply, but... I agree. Well stated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •