Database design
I built a system a couple years ago for electronic billing (credt card and electronic checking). In the database, I have an Account table listing every credit card or checking account we can bill with. Checking accounts have routing numbers, and credit cards have expiration dates, so I just included both fields, and put on a check constraint that one or the other has to be null.
There are a couple of other ways to do this, though. I could have added a CreditCard table and a CheckingAccount table, each with a primary key that's also a foreign key to Account. I also could have only had CreditCard and CheckingAccount tables, and done a union when I wanted to see all accounts (although that would have complicated the foreign keys of the other tables). Are there any accepted guidlines for how to handle this sort of situation?
I usually use the first two approaches, and choose between them by looking at how many fields there are that only apply to some rows. PostGreSQL hides the problem with their table inheritance feature, but they would have to deal with the same problem under the hood; how do they handle it?
There are a couple of other ways to do this, though. I could have added a CreditCard table and a CheckingAccount table, each with a primary key that's also a foreign key to Account. I also could have only had CreditCard and CheckingAccount tables, and done a union when I wanted to see all accounts (although that would have complicated the foreign keys of the other tables). Are there any accepted guidlines for how to handle this sort of situation?
I usually use the first two approaches, and choose between them by looking at how many fields there are that only apply to some rows. PostGreSQL hides the problem with their table inheritance feature, but they would have to deal with the same problem under the hood; how do they handle it?
