Normalisation vs If it works just do it!

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)I'm a bit confused... When I use the term "place" to describe a horse racing bet, I mean that I expect (and am wagering that) the horse will finish second. While it is unusual to do, you can "box" a single horse, meaning that you expect them to place in the top 3, but you retrieve your original wager plus twenty percent of what the return would have been for a correct bet (win, place, or show), which sounds something like your description of a "place" bet.

If you can describe what you really want, I'm sure that someone here on the forum can show you how to code it!

-PatP|||Hi,

Thanks for the response, the actual calculations is not the issue e.g. the odds for a place bet, that is already known.

The problem concerns how to read through the table of placed bets while calculating the profit/loss as we iterate through each record.

The arithmetic is not the issue here, its the method of retrieval so that each record will have a field containing a profit or loss amount, put there by SQL as it reads through each item.

Thanks.|||Performance is the key in my work. If running the cursor is going to slow down the operation then having a little redundency isn't bad.|||My thought would be to do an UPDATE using the CASE to control which computation you use (main or place). Clean, fast, no cursor... What more could you ask?

-PatP

Normalisation question?Help

I have an unnormalised data, and i would like to know if im normalizing the data correctly. It my homework, and its my first time doing normalisation as its so hard to understand. Thanks in advance.

UNF

Course code

Course title

Student Code

Student Name

Address

DOB

Telephone number

Module code

Module title

Tutor code

Tutor name

1NF

Student Code (Primary key)

Student Name

Address

DOB

Telephone number

-

Student Code( Foreign key)

Course code (Primary key)

Course title

Module code

Module title

Tutor code

Tutor name

2NF

Student Code (Primary key)

Student Name

Address

DOB

Telephone number

-

Course code (Primary key)

Student Code (Foreign key)

Course title

--

Module code (Primary key)

Module title

Tutor code

Tutor name

3NF

Student Code (Primary key)

Student Name

Address

DOB

Telephone number

-

Course code (Primary key)

Student Code (Foreign key)

Course title

Module Code(Primary key)

Student code (Foreign key)

Module title

--

Tutor code

Student code

Tutor name

In addition,

1. each course is taken by one or more studetns

2.each coruse is made up of one or more modules

3.a tutor may be teaching on one or more modules

4.each module must be delivered by one and only one tutor.

help pls..... :( im stuck

Normalisation Help

Not sure if this is the right place to put this message but, I am creating a stock control system for pc componenets, and need some help normalising the data, so that i can then implement the database.
The attibutes which need normalising are:

ProductID
Component
Price
Stock_level
Minimum_level
Reorder_amount
SupplierID
Supplier_name
Address1
Address2
Address3
Postcode
County
Email
TelNo
OrderCode
Quantity
Date_of_Order
Price
Quantity
Date
Total

If anybody could show me how to normalise this to third normal form, and show first and second Normal form, i would be very grateful.Hello,

is this a kind of homework ?

Think about the objects in the table ... there are
products, suppliers, addresses, orders ... these are the main objects.

Therefore you need a product, supplierer, address and order table.
The question is the relations between each object ??

Are there many prices for one product ??
Are there many adresses for one supplier and so on and so on

The other question ist ... what do you want with the second normal form ? Never heard, that somebody use this to create a physical database model ?!?!?!!?

Regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.comsql

Normalisation - Query

Hi all,
I need to design apet database whee customers get regeistered and then pst their pet deals. They are either for sale or just rehoming.

Can someone please take a few moments to have a look at what I have done so far and tell me if it is any good.

This is my 6th time re-designing the tables.

Many thanks.

ps: owners can be either Private or part of an Organisation.

Customer table:
Customer#, C_Fname, C_Lname, C.address1, C.address2, C.postCode, C.fax
C.country, UKlocation, C.Hphone, C.Mphone, C.e-mail, (rehoming-services?
yes/no)

Organisation details:
customer#, Org#, Org.website, Org.AreaCoverd,
Org.activities, Org.addinfo, Org.Logo

Members of Org details:
Member#, Org#, Member_name, Member_email, member_Phone, member_ext.

Host table
customer#, host#, animals accepted?, max_no_days, price_per_day

Pets table:
pet#, picture, p_type, p_breed

Permenant pets for rehoming
pet#, pet_perm#, name, p.color, p.d_o_b, age,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, microchipped.

pets for sale:
Pet#, sale#, no.ofMales, PriceperMale, No.ofFemales, PriceperFemale,what is the difference between a customer and a member? can a customer be more than one member? can a member be more than one customer?

what is the difference between a customer and a host? can a customer be more than one host? can a host be more than one customer?

and i don't understand how you can have "number of males" attached to a given pet

do you have an entity-relationship diagram for this structure? that would really help you straighten out which foreign keys go where

see Data Modeling (http://www.utexas.edu/cc/database/datamodeling/) for an excellent tutorial

rudy
http://rudy.ca/

Normalisation

I'm trying to normalise a badly designed database. I have created new tables that split one huge table up, and I think it is now in 3NF.

My problem is that once i've created the new tables, How do I get the data out of the huge table into the smaller ones. Are there SQL commands to do this? I'm using Oracle 8i.

Any help would be greatly appreciated.

Many thanks,

Francis.Hello,

there are many ways to do this :
For my explanation I use BIG as the name for the bigtable and SMALL1, SMALL2 for the new designed tables :

1)
Create a SQL statement that give you back the dateiled result of table SMALL1 and SMALL2

After that use
INSERT INTO SMALL1 (field1, field2...)
SELECT field1, field2 ... FROM big WHERE ...

The Select part is your designed Select statement .. to this with both queries ... OK ?

2) Export the datas with SQLLOADER and import them ... (see SQLLOADER documentation)

3) Write a PL/SQL procedure to select the datas from the big table and put them into the new one ...

If you have problems writing the procedure - just let me know - I will help you ...

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

*** did you ever use Oracle8i, MySQL, Interbase and Birdstep RDM Server databases at the same time ? No ? Use AlligatorSQL ***|||Thanks for you're prompt response.

I had thought of writing it as;

CREATE TABLE SMALL1(Column3, Column2, Column6)
AS
SELECT Column3, Column2, Column6
FROM BIGTABLE;

and do the same for all the other small tables. Then drop the big table.

Would this work?

Many thanks again.|||Hello,

thats perfect and very quick too ... I though, that you have already created the tables ...

Do it in this way :)

By the way ... are you interested in a new Oracle tool ... just send me an E-Mail to webmaster@.alligatorsql.com and I give you some information about AlligatorSQL.

Thanks

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com