Trouble with hierarchy design

1 Sep 2005 2:06 PM

tonicvodka

Hi all!

I have the following tables; Advertiser, Home, SubletWeek and Customer.

An advertiser can have one or many homes, a home can have one or many
subletweeks, a subletweek must have a customer.

An advertiser can also have one or many customers, a customer can have
one or many subletweeks.

This model doesn't feel clean, does anyone have a suggestion?

Thanks for any input,
Niclas

1 Sep 2005 2:25 PM

Brian Lawton

Have you considered whether a Customer can have more than one Advertiser?
Likewise, whether or not a Home can be listed with more than one Advertiser?
Also, is a SubletWeek a period of time represented as an attribute or an
actual entity? It sounds like you may need some for of temporal table to
track the set of valid SubletWeeks and then a separate table to track the
SubletWeeks a Home is assigned to a Customer.

--
--Brian
(Please reply to the newsgroups only.)

Show quote

"tonicvodka" <tonicvo***@hotmail.com> wrote in message
news:1125583573.807816.155960@g49g2000cwa.googlegroups.com...
> Hi all!
>
>
> I have the following tables; Advertiser, Home, SubletWeek and Customer.
>
>
> An advertiser can have one or many homes, a home can have one or many
> subletweeks, a subletweek must have a customer.
>
> An advertiser can also have one or many customers, a customer can have
> one or many subletweeks.
>
> This model doesn't feel clean, does anyone have a suggestion?
>
> Thanks for any input,
> Niclas
>

1 Sep 2005 3:16 PM

niclascolleen

Thanks a million for input!

What I missed to mention;
A Home can only have one Advertiser.
A Customer could have more than one Advertiser.
A SubletWeek is an entity containg Year, Week, Status, CustomerID and HomeID.

A temporal table sounds like a great idea, e.g. SubletWeekCustomer. Am I
getting correctly then?
But what do you mean with a table to track?

Best regards Niclas

--
Best regards,
Niclas Colleen

Show quote

"Brian Lawton" wrote:

> Have you considered whether a Customer can have more than one Advertiser?
> Likewise, whether or not a Home can be listed with more than one Advertiser?
> Also, is a SubletWeek a period of time represented as an attribute or an
> actual entity? It sounds like you may need some for of temporal table to
> track the set of valid SubletWeeks and then a separate table to track the
> SubletWeeks a Home is assigned to a Customer.
>
> --
> --Brian
> (Please reply to the newsgroups only.)
>
>
> "tonicvodka" <tonicvo***@hotmail.com> wrote in message
> news:1125583573.807816.155960@g49g2000cwa.googlegroups.com...
> > Hi all!
> >
> >
> > I have the following tables; Advertiser, Home, SubletWeek and Customer.
> >
> >
> > An advertiser can have one or many homes, a home can have one or many
> > subletweeks, a subletweek must have a customer.
> >
> > An advertiser can also have one or many customers, a customer can have
> > one or many subletweeks.
> >
> > This model doesn't feel clean, does anyone have a suggestion?
> >
> > Thanks for any input,
> > Niclas
> >
>
>
>

1 Sep 2005 2:36 PM

Rakesh

Advertiser - a, Home - h, SubletWeek - s and Customer - c, 1/m stands for
1/many relationship

An advertiser can have one or many homes, a home can have one or many
subletweeks, a subletweek must have a customer.

a 1/m h - I
h 1/m s - II
s 1 c - III
--------------------

An advertiser can also have one or many customers, a customer can have
one or many subletweeks.

a 1/m c - I & II implies this
c 1/m s - III implies this

So u focus on I, II & III

Ignore the ones which r already implied by I, II, & III

Rakesh

Show quote

"tonicvodka" wrote:

> Hi all!
>
>
> I have the following tables; Advertiser, Home, SubletWeek and Customer.
>
>
> An advertiser can have one or many homes, a home can have one or many
> subletweeks, a subletweek must have a customer.
>
> An advertiser can also have one or many customers, a customer can have
> one or many subletweeks.
>
> This model doesn't feel clean, does anyone have a suggestion?
>
> Thanks for any input,
> Niclas
>
>