Trying to design a database and getting stuck

Will · May 2nd, 2009, 07:43 AM

Ok I am new to database design (this is my first one) and so far all I know about it is what I have learnt from beginning ASP.NET 3.5 in C# and VB. I have bought the book Beginning SQL Server 2005 programming as I didn't think I would need to be doing much database design but as luck would have it that is what i have to do now.

So any help anyone could lend with this would be very..helpful.

I need a database which will store client information, centre information, and information that has been entered into a contact form. The database needs to contain details of which centre (1 of 3) a client is going to use and when and how long they will use it for. I have had a go of this and here it my attempt below:

(Column name will be followed by datatype. Nulls not allowed unluess otherwise specified.)

Table: Clients
Id: int, IsIdentity(yes)
FirstName: nvarchar(50)
LastName: nvarchar(50)
ChildName: nvarchar(50)
ChildName2: nvarchar(50) null
ChildName3: nvarchar(50) null
Centre: nvarchar(50) Foriegn Key constraint to the centres table.

Table: Centres
Id: int, IsIdentity(yes)
Date of centre use: ?
Arrival Time: ?
PickUp Time: ?
Centre Name: nvarchar(50)

So the idea is. A client will fill out a form on the website with the details for the first table this information will then be written to the Clients table above.

The second table the user will pick a date and time using a calendar control and that information will also be written to the second table. I then need the business owner to be able to print out the weekly schedule so she knows for example that on Monday she will have a total of 20 childeren and what times they will be coming etc. I hope that is clear. So I was thinking of maybe a page with like a ListView/gridview that sorts the information in chronological order of Time Monday to Friday, AM-PM. Would I need another table for that or are the 2 I already have satisfactory?

Thats alot of information to take in and probably alot of work so you could help me by:

Is what I want to do possible?

Does the way I want to do it sound like a good idea?

What datatypes for the columns with "?" in my table designs do I need?

Anything else that I will find helpful, links resources that you know of would be a great help. Thanks in advance for anyone who does help I realise I waffled abit but I have tried to be as complete as possible.

thanks again

Old Pedant · May 2nd, 2009, 05:39 PM

Ummm...why do you only provide for 3 children per family??? You should have a separate table for the children, ONE child per record, with a foreign key back to the clients' id field.

This field makes no sense:
Centre: nvarchar(50) Foriegn Key constraint to the centres table.

The primary key on the centres table is an int, so any foreign key to it should also be int. But in any case, it seems to me like the *direction* of the keys is backwards.

As you have this coded, it would mean that any given client could only use a centre *ONE TIME* and one time only. Surely this isn't what you are after???

I *THINK* these are the tables you really need:

Code:

Table: Clients
    clientid : int, PK, autonumber
    lastname : varchar
    firstname : varchar
    username : varchar (or maybe email address?)
    password : varchar (so they can log back in later to add/edit/delete)

Table: Children
    childid : int, PK, autonumber
    clientid : int, FK to Clients table
    lastname : varchar [not all children have same last name as their parents!]
    firstname : varchar

Table: Centres 
    centreid : int, PK, autonumber
    name : varchar
    ... other info such as proprietor, address, etc. ?? ...

Table: CentreUse
    centreid : int, FK to Centres table [that is, which Center is this for]
    childid : int, FK to Children table [that is, which kid is coming to the centre]
    Arrival : DATETIME [full date *AND* time of expected arrival of the child]
    Pickup : DATETIME [full date AND time of expected pickup of the child]

*NOW* I think you are ready to proceed.

A client signs up (or logs in if this is a repeat visit to the site?). He/she gives a full list of all his/her children. (No longer limited to just 3!)

On the main page for clients, he/she can click checkboxex to register ONE OR MORE children, a radio button to choose a centre from a list, and then use the calendar control and clock control to pick a data and arrival/pickup times. Submit that form and it writes one record *per child* to the CentreUse table.

After that, it's easy. The query to get the calendar for the week is something like this:

Code:

SELECT CU.Arrival, CU.Pickup, C.Lastname, C.firstname
FROM CentreUse AS CU, Children AS C
WHERE CU.childid = C.childid
AND CU.centreid = 73
AND CU.Arrival >= '2009-05-03' AND CU.Arrival < '2009-05-10'
ORDER BY CU.Arrival, C.Lastname, C.Firstname

And in your code (PHP? ASP? JSP?) to create the weekly calendar, you simply loop through all the records, detect when the date of the Arrival field changes and start a new daily calendar (might be just a break in the weekly <table>, whatever).

Will · May 3rd, 2009, 08:09 AM

Firstly thanks for taking the time to read through and help me. As you probably gathered I am very new to databases. I will look through what you have written and try and learn and understand it but might have a few more questions further down the road.

Thanks again for your help.

Will · May 6th, 2009, 11:46 AM

Ok as expected I am having a little trouble. I have built the database fine thanks to your help but now I am getting abit stuck on writing my statements...

I am coding in asp.net 3.5 and my first bit of trouble is with an insert statement..

The code is below:

Code:

'declare string
        Dim strFirstName As String
        Dim strLastName As String
        Dim strEmail As String

        'Assign textbox value to string variable
        strFirstName = txtFirstName.Text
        strLastName = txtLastName.Text
        strEmail = txtEmail.Text

        'Create a connection object
        Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ChillOutOfSchool.mdf;Integrated Security=True;User Instance=True")

        'create a command object
        Dim sqlComm As New SqlCommand()

        'INSERT statement?
        sqlComm.CommandText = "INSERT INTO Clients (FirstName, LastName, EmailAddress)VALUES ();"
        sqlComm.CommandType = Data.CommandType.Text
        sqlComm.Connection = sqlConn

        'Open the connection
        sqlConn.Open()

        sqlComm.ExecuteNonQuery()

        sqlConn.Close()

The idea is that I have stored the textbox values in the objects strFirstName, strLastName and strEmail however how do I then edit my INSERT statement to insert the values that these objects hold instead of their face value? ie strFirstName inserts strFirstName.

gbianchi · May 6th, 2009, 12:36 PM

Just to make you search a little in google..

The better way is with parameters (use a parametrized query)..

The other way, did you ever heard about concatenating strings??? (and again, google is a good friend!!!)

Will · May 6th, 2009, 01:38 PM

Ok so this code works :) is this what you meant about parameters?

Code:

sqlComm.CommandText = "INSERT INTO Clients (FirstName, LastName, EmailAddress)VALUES (@FirstName, @LastName, @EmailAddress);"
        sqlComm.Parameters.AddWithValue("@FirstName", strFirstName)
        sqlComm.Parameters.AddWithValue("@LastName", strLastName)
        sqlComm.Parameters.AddWithValue("@EmailAddress", strEmailAddress)

And for the concatenation you meant something like

[CODE
sqlComm.CommandText = "INSERT INTO Clients (strFirstName) &strFirstName.Text
[/CODE]

That is a guess as I did the paramter search first. Thanks a bunch anyways I have been trying to figure out this all day it's just I didn't know what it was called that I was looking for.

Which can make finding something difficult.

Cheers again.

gbianchi · May 6th, 2009, 01:40 PM

The parameters are good.
Concatenation of strings is something that comes in every language.. and is something like what you try to do, but not exactly that...

Will · May 6th, 2009, 01:58 PM

Yeah I have heard of it before in Imars book but I am still learning. I will defiantly revisit it again later on. But I have been at it pretty much all day now and need a rest lol.

Thanks again for your help.

Will · May 7th, 2009, 06:38 AM

Ok again I am having trouble understanding the statement that was provided to me by Old Pedant.

Here it is below:

SELECT CU.Arrival, CU.Pickup, C.Lastname, C.firstname
FROM CentreUse AS CU, Children AS C
WHERE CU.childid = C.childid
AND CU.centreid = 73
AND CU.Arrival >= '2009-05-03' AND CU.Arrival < '2009-05-10'
ORDER BY CU.Arrival, C.Lastname, C.Firstname
I get that it is taking columns from the tables centre use and Children and then taking the relevant columns Arrival, Pick Up, Lastname & Firstname. Making sure that they correspond so that the child being selected from the centre use table has the correct first and last name displayed.

But I don't understand " AND CU.centreid = 73 ". Where did 73 come from what is it's purpose?

After that line it is then saying that Arrival needs to be greater than or equal to 2009-05-03 and less than 2009-05-10? So this selects 7 days for the week coming up?

The last line states in what order the information is to be displayed.

That is my understanding of the statement and I don't think I am far off, it is just that one bit that I don't understand.

The next part of my question is where should I put this statement? I think I have 2 options here, either create a stored procedure and call it in the code or write the statement in the code directly? Is one method preferred over the other or is it a case of horses for courses? In which case what horse and course do I need?

In what way is the information then displayed? Can I put it in a gridview or listview control? Or do I not need too?

I am still googling and will post if I answer any of my questions but any other help, even be it some hints would be great.

Thanks again for taking the time to help me out. Cheers

gbianchi · May 7th, 2009, 07:53 AM

Fast answers:

You are still in the sql forum, so I will have to say that a SP is the best way to do it.
Every method has his con and pros, but I will say that is depends on how big your application is and how many people will need to code on it.
Cu.centreid indicates which centre you are loking for. You should parametrize it. The dates should be parametrized too.

How do you want to display it depends totally on you. You can display it the way you like, It's doesn't depend on what query you are making.