Re: Entity relationship and tables help please?

"Wacjac?" <sorry_at_nospam.com> wrote in message news:<a5dqlk$k9k$1_at_newsg4.svr.pol.co.uk>...
> Hi there - if anyone has a few spare minutes I could really do with some
> help please.
>
> I'm a student and have been given an exercise using MS Access to design a
> simple database for a fictional dating agency. This database is not
> concerned with accounts or administration purely the matching of clients and
> meeting management.

I assume you've already completed said exercise, but perhaps these thoughts of mine might help on your next.

>
> I have racked my brains to come up with an entity relationship diagram but
> cannot get past the stage of even selecting the entity tables to use. I
> simply cannot decide whether the males and female should be in seperate
> tables with the intersecting table being interestes/hobbies and/or areas
> which they are from - or am I completely barking up the wrong tree here?

Problem 1. Are males and females seperate entities or attributes of a "Person/Customer" entity? Let's have a look at your next statement here...  

> Assuming it's purely a hetrosexual database ...

1. Well why are you making this assumption? If you're unsure about this you should be checking with the customer (i.e. the dating agency owner).
2. Even if the customer wants a hetro only dating service, you can guarantee that 2 months after sign-off they will come back to you and ask for it to be changed to handle all sorts of other relationship types. If you've "hard wired" a hetro only database then you're going to have a mission expanding it later. But if you remember the old adage "the customer doesn't know s**t" then you'd have foreseen this circumstance and built your database with that in mind.

Ok, now for my opinion. One table "client" with attribute "gender".

Why?

Are you comfortable with the difference between logical and physical data models?

Step 1. EVERY time I model data I go whole hog on the logical data model, normalize everything and throw in all the sub-classing I can see as well. After that exercise I may end up with a diagram where clients are sub-classes into male and female.

Step 2. Then I look at how I'd physically implement that database, how much time it would take, head aches it would cause, can the project budget cope with it? Will it confuse the hell out of other developers (who wouldn't know an ER from an RE) if I do it that way? Am I going to be able to implement my data model using the database software I will be using? And ultimately I compare all these answer to the question - can I justify the added cost of 2 tables (which will require twice as many admin screens) when a single field will require only a radio button on 1 admin screen?

Usually the answer is no ... so I cheat (cheating = doing it the cheapest way that will work without selling your soul, rather than the way you'd do it if you had infinite budget) and use views (in Access - Query) instead.

i.e.

select * from client where gender = 'm'

select * from client where gender = 'f'

Of course another indicator of whether something should be an entity or an attribute is whether the seperate entities have most - or all - other attributes and relations in common. Here is a similar and typical dilema of mine.

I have a 'discount' table that represents the many-to-many relatioship between a customer and a product. It is many-to-many because the dscounts only apply over a certain amount of time (enddate, begdate), but as well as this they are of different types, 'fixed price discount' (ie. a certain number of dollars off) and 'percent discount'. So I have 2 ways to implement this...

create table discount
(
prod_id int
cust_id int
beg_date datetime
end_date datetime

type       char(1)
value      real

or

create table discount
(
prod_id int
cust_id int
beg_date datetime
end_date datetime
type char(1)
)

create table discount_fixed
(

prod_id    int
cust_id    int
disc_value real

create table discount_percent
(

prod_id    int
cust_id    int
disc_perc  real

I think you can tell which one I use most often. (PS. No I would never give fields such useless names either).

Lastly. This goes for real projects as much as it does for academic ones. Whatever design decisions you make, be sure you accompany your design with all the assumptions and exclusions you made. There is always going to be more than one way to do something, but if you explain why you choose the method you did then you've covered your arse. This tends to highlight things that you have erroneously assumed as well.

Good luck

:)

>
> Assuming it's purely a hetrosexual database I'd be grateful for any expert
> opinions as to what you think the tables should be. We are told between 3
> and 5 tables will do this.
>
> To quote a small section from the question:-
>
> "She (the dating agency owner) feels that if the client data is stored on a
> database then it would enable her to produce a short list of potential
> matches and she could then use her experience to determine which would be
> the best matches. A considerable amount of personal data is held about
> clients and it is becoming increasingly difficult for her to remember
> everyone. It may also make it easier for her to keep track of which clients
> have met and when. This will help her keep track of active clients and
> identify those she has not seen for some time and help her follow up on
> those inactive clients.
> Clients pay a joining fee, an annual subscription, and also a small fee for
> each meeting that is arranged"
>
> Once I have got my head around which way to go I am quite competant at
> building the database.
>
> Does anyone have any thoughts or ideas to help please?
>
> Thanks in advance.
>
> Wacjac
>
> (apologies for the cross-posting - just I need to get a good concensus of
> opinion here)
Received on Mon Mar 18 2002 - 02:58:57 CET