Mapping Problem
Hi,
I need some help in design of some database tables and theirs mapping into classes (not a Hibernate mapping).
Maybe someone can advise me.
The task is as follows:
In my software (Java + MySQL) I have two main participants: supplier and dealer.
I store suppliers and dealers in two tables:
SUPPLIER (id, name, etc)
DEALER (id, name etc)
suppliers and Dealers can have users.One user have login, password, email etc. and can log on to my reporting software.I decide to store users into one table:
USER (id, login, password, email etc)
Although I have to distinguish between supplier's users and dealer's users because I should show them different views
That's why I create a new table :
USER_MAPPING (id_user, id_supplier, id_dealer)
Only one of id_supplier or id_dealer can be not null.
So I can distinguish between both kind of users.
Question 1: What is your opinion about this design?
Question 2: Which is the best strategy to map these tables into class hierarchy?
Many thanks in advance
PS
Excuse me my english
[1143 byte] By [
allex_poa] at [2007-11-27 6:35:12]
Hi fastmike,
Thank you for your fast reply.
No, suppliers and dealers have no connection (they are totally independent each other).
Each of them can have users.Users can log on in a web interface and can view different reportings.When a user logs on I should determine if this user is an supplier's user or an dealer's user.That's why I create the table user_mapping, which shows who is the "owner" of the user (id_supplier and id_dealer are FK's and are defined as "integer null", i.e. they can be null).So if we have user with id 2 and in user_mapping the following record (id_user, id_supplier, id_dealer) -> (2, 3, null), then the user belongs to the supplier with id 3.
Thanks
Hi,
Suppliers are companies which have products.This products should be sold.Dealers are companies which sale products.Supplier delivers products but does not know which dealer sales the product.The only important thing for the supplier is the revenue.The dealer sales products and also does not know who is the supplier.And again the only important thing for the dealer is the revenue.So the software should distribute supplier's products between dealers and finally should show all the relevant revenue information in a web interface.Each of the parties (suppliers and dealers) have employees.These employees are called users in terms of my software.One employee/user can log on to the web interface and track the sales, revenues etc.
I decide to store all the users in one table, because from authorization point of view they are equivalent (for instance they can log on)
Although I should somehow distinguish between supplier's users and dealer's users (the table USER_MAPPING)
Each of the user have also roles, but these roles control just if an user can see/request some view/gui or not, they can not control the requirement that for instance dealer A can see only his own trades and not the trades of dealer B.So there exists also permission on the "data level".With the table USER_MAPPING I try to implement this requirement (permission on "data level").That is, when a user logs on, I should recognize if he is an supplier's user ot dealer's user and then I can show them just the trades for its supplier or dealer.
I hope, I managed to explain my goals
Thanks
