Hi All

I am restructuring a company's DB and web-based ordering system. The
DB contains separate tables for
1. Users (who may or may not be customers and/or users)
2. Customers (who may or may not be customers and/or users)
3. Contacts (who may or may not be customers and/or users).

Part of the restructure involves enabling current customers to view/
edit their orders and details online where previously only Users (who
typically staff) were able to do this.

Question:

Should the redesign:
a. Keep tables separate, or
b. Combine the tables into a single table

Note that all types may eventually become users and that separate
tables are planned for roles and permissions.

Any advice welcome

Thanks
Adrian

Adrian:
> Hi All
>
> I am restructuring a company's DB and web-based ordering system. The
> DB contains separate tables for
> 1. Users (who may or may not be customers and/or users)
> 2. Customers (who may or may not be customers and/or users)
> 3. Contacts (who may or may not be customers and/or users).
[...]
> Should the redesign:
> a. Keep tables separate, or
> b. Combine the tables into a single table

Well, you haven't told us yet:
- what the difference between those three groups is
- why you want to define these three groups
- how a member of one group becomes member of another group

In other words: why not one table "ThemFolks"?

--
Erick
[concerned that you are talking about tables, and not about entities in
your ER diagram]

Adrian wrote:
> Hi All
>
> I am restructuring a company's DB and web-based ordering system. The
> DB contains separate tables for
> 1. Users (who may or may not be customers and/or users)
> 2. Customers (who may or may not be customers and/or users)
> 3. Contacts (who may or may not be customers and/or users).
>
> Part of the restructure involves enabling current customers to view/
> edit their orders and details online where previously only Users (who
> typically staff) were able to do this.
>
> Question:
>
> Should the redesign:
> a. Keep tables separate, or
> b. Combine the tables into a single table
>
> Note that all types may eventually become users and that separate
> tables are planned for roles and permissions.
>
> Any advice welcome
>
> Thanks
> Adrian


So, you took on a job for which you were not qualified and expect the
user community - a number of whom are IT/DB professionals - to do your
job for free? My fee is $75hr for this sort of work...

On Apr 19, 4:40pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
wrote:
> Adrian:
>
> > Hi All
>
> > I am restructuring a company's DB and web-based ordering system. The
> > DB contains separate tables for
> > 1. Users (who may or may not be customers and/or users)
> > 2. Customers (who may or may not be customers and/or users)
> > 3. Contacts (who may or may not be customers and/or users).
> [...]
> > Should the redesign:
> > a. Keep tables separate, or
> > b. Combine the tables into a single table
>
> Well, you haven't told us yet:
> - what the difference between those three groups is
> - why you want to define these three groups
> - how a member of one group becomes member of another group
>
> In other words: why not one table "ThemFolks"?
>
> --
> Erick
> [concerned that you are talking about tables, and not about entities in
> your ER diagram]

Good point - the objects in the domain model are:

1. Customer - who has orders for products and collects to ordered
items from a delivery location
2. User - who can login, edit, add orders and various admin functions
depending on role/permissions
3. Contact Person - who acts as the point of contact at a delivery
location

At present no customers are users (ie have online access) but purpose
of the re-development is to provide customer with a user account.
Contact people are often customers but not necessarily.

Thanks

Adrian [on Sat, 19 Apr 2008 10:08:10 -0700 (PDT)]:


> 1. Customer - who has orders for products and collects to ordered
> items from a delivery location
> 2. User - who can login, edit, add orders and various admin functions
> depending on role/permissions
> 3. Contact Person - who acts as the point of contact at a delivery
> location

So, you have 'people' who are at least a Contact...someone you have to
deal with, one way or the other.
Add a few relationships (hasOrdered, mustPay) and attributes (billing
address, customerNumber) and he is a Customer. Add some more (uID,
password, memberOfDepartment) and he is a User.

Barring any repetitive attributes, you may consider putting it all into
one table, leaving the superfluous columns NULL. The alternative would
be one table for all common fields, and two more for the additional
fields of Customer and User.

Me? I would let it depend on the company's dynamics. If you expect more
roles or 'people types' in the near future, then I'd go for the smallest
common table possible, and additional tables for specific 'people type
fields'.


--
Erick

"Tell me what you need, and I'll tell you how to get along without it."

On Apr 19, 6:54pm, Erick T. Barkhuis <erick.use...@ardane.c-o-m>
wrote:
> Adrian [on Sat, 19 Apr 2008 10:08:10 -0700 (PDT)]:
>
> > 1. Customer - who has orders for products and collects to ordered
> > items from a delivery location
> > 2. User - who can login, edit, add orders and various admin functions
> > depending on role/permissions
> > 3. Contact Person - who acts as the point of contact at a delivery
> > location
>
> So, you have 'people' who are at least a Contact...someone you have to
> deal with, one way or the other.
> Add a few relationships (hasOrdered, mustPay) and attributes (billing
> address, customerNumber) and he is a Customer. Add some more (uID,
> password, memberOfDepartment) and he is a User.
>
> Barring any repetitive attributes, you may consider putting it all into
> one table, leaving the superfluous columns NULL. The alternative would
> be one table for all common fields, and two more for the additional
> fields of Customer and User.
>
> Me? I would let it depend on the company's dynamics. If you expect more
> roles or 'people types' in the near future, then I'd go for the smallest
> common table possible, and additional tables for specific 'people type
> fields'.
>
> --
> Erick
>
> "Tell me what you need, and I'll tell you how to get along without it."

Thanks Erick

I'm leaning toward a single table soln - I guess I was just facing the
question of object model vs data model and which first - an issue that
I have now seen is extensively discussed on the web.

Cheers
Adrian