In my view, when you have a one-to-many relationship like users to enterprises, you should always design the database to handle *any* number of occurrences (enterprises in this case). The standard way of doing that would be to normalize to Third Normal Form. Your current design isn't even in First Normal Form since it includes a repeating group (Enterprise repeats twice). What will you do when one of your users suddenly has 4 enterprises? Or 10? Maybe it won't happen right away but it almost certainly *will* happen eventually.
That is not to say that repeating groups are always wrong. There are cases where denormalization to something like what you describe is acceptable. However, the normal advice given on database design courses is to normalize everything to Third Normal Form (or even Fourth Normal Form), then denormalize selected cases ONLY WHEN YOU CAN MAKE A STRONG SPECIFIC PERFORMANCE ARGUMENT IN FAVOUR OF THE DENORMALIZATION AND ARE PREPARED TO PAY FOR THE EXTRA OVERHEAD INVOLVED. The resulting design would look like this: Users ------ user varchar enterprise int primary key(user, enterprise) foreign key(enterprise) references Enterprise(code) Enterprise ----------- code int autoincrement description varchar primary key (code) The data would look like this: Users ------ George 1 George 2 Rene 3 Simone 4 Enterprises ------------- 1 Hotel Paris 2 Camion de Location Marseilles 3 Brasserie Levesque 4 Boutique Simard To get all the enterprises owned by a given user: select user, enterprise from users u inner join enterprises e on u.enterprises = e.code This query will return one row for each enterprise owned by the user, no matter how many enterprises he or she owns. This design also eliminates the question of what data to store for the second enterprise when a user has only one enterprise; that will never happen. Rhino ----- Original Message ----- From: "Pierre-Etienne Mélet" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 13, 2004 6:42 PM Subject: Help about a db > Hi all, > > I have the following data > > Users : user(varchar), entreprise_1(int), entreprise_2(int) > Entreprise: idx(int, prim, auto_incr), code(varchar) > > some users have one entreprise, and some two. > > if I decide that 'no entreprise x' is coded with entreprise_x=0, i have > obviously a problem with > > SELECT Users.* > FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 > WHERE > entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx > AND (Entreprise1.code='CODEX' OR Entreprise2.code='CODEX'); > > since > > SELECT Users.* > FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 > WHERE > entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx > > returns 0 tuples when entreprise_2=0 > > In fact, i'm looking for the smartest way to code 'nothing'. > For the moment, i've created the Enterprise 'dummy' (idx=0, > code='VOID'), but i absolutely not satisfied with this solution. > > Any idea ? > > Thank you all by advance > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
