Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Letters should be found in a string - what is the best?
Message
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00428574
Message ID:
00429044
Vues:
24
>Hi!
>
>>Hi Andrew,
>>
>>If you can explain your first idea more detailed, it would be great.
>
>Nadya, it looks like you don't know RDBMS theory well ;) Sorry, just kidding. I will try to explaing it by easy way.

Actually, I know RDBMS theory, or at least thought, what I knew :) Just had a brain dead, because the table already contains 1,8 mln. record. Say, 1/3 of them has not empty amenities field and more than 1 code in it.
M-M would be BldgID AmenityCode > 2,5 mln records :)
and query would be like you described :)

Don't see benefits here... :)
>
>In RDBMS theory many-to-many relationship should be established by using separate additional table to preserve third normal form of database. For example:
>
>Customer
>Cust_id name address
>
>Product
>prod_id name description
>
>Many-to-many relationship will be done using third table
>
>CustProd
>Cust_id Prod_id date_bought
>
>
>As you see, this way allows also add some additional information. You way will look here like following:
>
>Customer
>Cust_id name address mapto='A8', 'BCD' etc.
>
>Product
>prod_id = 'A', '8', 'B' etc.
>
>Do you see difference?
>
>Anyway, seems you will not accept such database changes because you already have a lot of data, as you said. In addition, third notmal form for Many-to-many relationship is good for flexibility, but bad for queries. Your query in such case for 'AND' will look like following:
>
>Select * from Customers
> Where cust_id IN (select cp.cust_id from CustProd cp inner join products p
> on p.prod_id = cp.prod_id where prod_id = 'A')
> AND cust_id IN (select cp.cust_id from CustProd cp inner join products p
> on p.prod_id = cp.prod_id where prod_id = '8')
>
>Nice, heh? I don;t know about speed, however. Last query definitely will run more quickly on SQL server, but will be slower with VFP database through network because plenty of indexes for optimization.
If it's not broken, fix it until it is.


My Blog
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform