advice on altering a table
-
hiya,
I inherited an Access database table.It's for a
dog breeding site, and it lists the people who are dog owners / dog breeders.
The main purpose seems to be the address details of the people, coupled with some bitFields to denote whether they're breeders/owners / both.
I have 2 issues with the table:
1) it seems to be doing too much
2) it's conceivable that a person could find themselves in this table, even though they're neither a breeder / owner
I think that I should maybe split it into
3 tables:
tblPersonAddresDetails
tblBreeder
tblOwner
I was wondering if anyone could spare any comments?
cheers,
yogi
-
I'm going to agree with you...it's common to see bit fields keeping track of attributes, when more properly it should be one-to-many tables.
you could envision that if you had to add "Kennel Owner" as a new field to capture, it might be easier to add a bit field, but it would be better to add it as a separate table and relate the person to it instead.
I'd end up creating a View/Views(s) that had the information as a yes/no bitfield whatever for reporting purposes, but design wise, it'd be as you described.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I tend to agree with Lowell that if you need a flexible design and might have changes, it makes some sense to add a "Breeders" table and link people to it. There are probably attributes for Breeders that don't need to be in that table.
-
Yogiberr - congratulations on being the recipient of a passed buck.
What you have not shared with us, is what leads you to conclude what the primary purpose of the database is. I assume it's accessed (no pun intended) from a web page? Are there existing reports that help substantiate your conclusions? Or is it basically a lookup table for dog breeders / owners / wannabe breeders / wannabeowners ?
IF an individuals bitflags are set to no for breeder and no for owners, then that would tell you whether anyone is in the database but not falling in either category.
There's a temptation to simply 'convert' from one platform to another without doing much in the way of analysis. But wearing such blinders can cause you much pain before you're done.
You don't need to share all the many details behind your conclusion, I'm just trying to remind you of things that you may not have paid sufficient attention to. If you have paid attention, great. If not, this is a friendly reminder.
-
I have 2 issues with the table:
1) it seems to be doing too much
2) it's conceivable that a person could find themselves in this table, even though they're neither a breeder / owner.
Though I don't know the rest of how your database is, at first glance I'd say that it has been designed just right.
Because the Persons table details will be more or less similar for both owners and breeders, it makes more sense to keep them together, especially since one person can be both or neither. Makes for simpler storage, faster retrieval and neater reporting.
Have you paused to consider how your data would look if you proceed to split it? Won't you have to duplicate some records across the two, with address details in the third? And how will you ensure standardisation as regards to uniform spelling of names, etc?
Do you not see the possibility of tying yourself up in knots when it comes to displaying data and compiling reports?
As a programmer-cum-analyst-cum-DB developer, I can tell you that database design is the single most critical factor in software development.
-
Person is one entity, which may or may not have attributes of breeder / owner etc. Keep persons together in one table.
Whether or not you decide to create tables to hold the extra attributes separately from the person table will surely depend on their number and complexity? If you only have yes/no and maybe start/end date, is it really worth creating separate tables to hold those few attributes? As goodguy points out, your current layout is far easier to display and report from, than if you split the data out.
Addresses are different: a person may have more than one address. A breeder for instance is likely to have separate home and kennel addresses, and possibly even a third address for shop. It's almost standard practice to hold addresses in a separate table.
Just my 1p worth.
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
hiya,
Thanks for all the comments.I use an OR mapper, so the client code isn't too hard, just predicates.
I've been informed that the owner / breeder should still be classed as an owner / breeder, even if they don't currently own/ breed any dogs.
So, the table will stay as-is for the moment.Your comments were much appreciated.
yogi
-
1. You have to follow the business rules
2. You may want to inquire as to whether there are any dates that might be useful to retain - for instance, last reported activity. Someone who last bred dogs 30 years ago may actually have stopped breeding and no longer needs to be retained (assuming this incarnation of the database will stay around that long). There are probably improvements that could be made to the process, but I suspect that the total data volume is small enough that performance issues won't matter and this might be a law of diminishing returns issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply