|
Thank you! What I have in mind for example is the product browser of the website http://www.farnell.com This site is huge. Mine will be very small with not that many items (compared to Farnell) and with few users.
I have difficulty to accept the idea with NULLable columns yet, because because I might end with dozens and dozens if not hundreds of columns.
Thank you for the remark. A friend of mine also told me: "Forget about that OOP way of thinking". I'll think about my problem a little while longer.
|
|
|
|
|
You could have one table of Categories and another of CategoryProperties (multiple rows for each Category, containing CategoryId, Property Name and Value) - will that work for you?
|
|
|
|
|
I don't understand how to do that yet.
|
|
|
|
|
For example...
Table 1: Category
Fields: CategoryId (PK), CategoryName
Sample data:
1 Car
2 Apples
Table 2: CategoryProperties
Fields: CategoryId (FK), PropertyId (PK), PropertyName, PropertyValue
Sample data:
1 1 HorsePower 1500
2 2 Sort 10
Hopefully, the above clarifies my thought on this... don't know if it helps you.
|
|
|
|
|
Thank you! Interesting suggestion. I'll think about it. It could do the job!
|
|
|
|
|
Hi guys!
I am having a problem generating an overviewwith information from 3 different tables.
This is the situation:
There is a table (Users) with a list of all Users. These users can create their own groups and add users to these groups. So there are different groups with different users added to it for each user.
I have the following tables:
Users
user_id
user_name
data
1,Demo1
2,Demo2
3,Demo3
4,Demo4
5,Demo5
6,Demo5
Groups
group_id
group_user_id (Users.user_id who owns this group)
group_name
data
1,1,Design
2,1,Support
3,1,Sales
4,2,tmp_demo2
Groupmembers
groupmember_group_id (Groups.group_id)
groupmember_user_id (Users.user_id)
data
1,2
3,2
1,4
Now I want to generate an overview for a specific user (in this case: user_id #1), show ALL users in a list including their Groups.group_name if added to a group (created by user_id #1).
When using this query
SELECT Users.user_id, Users.user_name, Groups.group_name
FROM Users LEFT OUTER JOIN
Groupmembers ON Users.user_id = Groupmembers.groupmember_user_id LEFT OUTER JOIN
Groups ON Groupmembers.groupmember_group_id = Groups.group_id AND Groups.group_user_id = 1
I get the following data
1 demo1 Design
1 demo1 NULL
2 demo2 NULL
3 demo3 Support
4 demo4 NULL
5 demo5 NULL
6 demo6 NULL
Why do I get user_id #1 dubble? I guess it has something to do because user_id #1 had is added to two different groups, but form different users. I tried using DISTINCT, but no results. How can I generate a correct overview?
Thanks in advance!
|
|
|
|
|
What should display here ? Can you post your assuming results result ?
Can clarify on your result or requirement ?
Parwej Ahamad
R & D with IIS 5.0/6.0
|
|
|
|
|
1 demo1 Design
2 demo2 NULL
3 demo3 Support
4 demo4 NULL
5 demo5 NULL
6 demo6 NULL
This is what I would like to get:
- An overview of ALL users (not any double users)
- Name of the group the users are added to. BUT this group must be created by user_id #1 (in this case)
|
|
|
|
|
Hello all,
I have Typed-DataSet with 2 TableAdapters one for grades and one for courses.
The TableAddapters have relation, so when I use grade row I can get the parent row from a property:
foreach (DataSetCollege.GradesRow var in grades)
{
Response.Write( var.grade + var.CoursesRow.crsName);
}
My problem is that the var.CoursesRow is always null
How do I use this property?
Thanks, Roy.
|
|
|
|
|
Hi,
It seems that courses TableAdapter is not getting populated therefore, please double check whether data is coming in TableAdapter or not.
Hope this helps
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Hello Every one i have a problem ,i want to selct the all record from one table and get the limited recode from the other table and combine then with a query like
SELECT * FROM Personal p INNER JOIN DailyRecord dr ON p.pCODE = dr.pcode INNER JOIN Department dept ON p.deptid = dept.deptid where cast(CONVERT(datetime,dr.TIMEIN,105) as datetime) between cast('2008-06-05' as datetime) and cast('2008-06-05' as datetime)+1
when i run this query it only returns limited rows if i remove the last where condition then it gives me the whole table with joins but i want to get all data from pernal and the time in out from daily record if avlaible if not then its null in given dates
|
|
|
|
|
My guess is you are using the wrong join logic based on what I can deduct from your post.
Try using LEFT JOIN instead of INNER JOIN.
|
|
|
|
|
i am very thankfull for your replay i also used left join,left outer join but when its comes on where its only selct the limited like between these record and eleminate all other record
|
|
|
|
|
Move the content of the WHERE clause up on the relevant LEFT JOIN ON clause.
|
|
|
|
|
Does it make any performance benefit if we split our records across more tables instead of just one table ?
|
|
|
|
|
there could be... provided the database is hosted on raid and the tables are located in diffetent physical files.
|
|
|
|
|
Member 3301325 wrote: the tables are located in diffetent physical files.
How can I know if the tables are located in different physical devices and how can this be possible at all ? I meant the tables in question belong to one database.
|
|
|
|
|
this is possible even if they are in same database.
alll relational databases, sql server/oracle supports this. create separate tablespaces and let the tablespaces be in separate files in separate disks...
& then create the tables in separate tablespaces...
|
|
|
|
|
Member 3301325 wrote: create separate tablespaces and let the tablespaces be in separate files in separate disks...
& then create the tables in separate tablespaces...
Can you give me a clue how I can create table spaces in sql server and create tables in them ?
|
|
|
|
|
Yes, split the records in more than one table is a part of normalization.
I am sure if you keeps records in a single table then defintely it will consist redundant (duplicate) information. So that is why we split our records across more that one table on the basis of any keys. This is the part of normalization.
Parwej Ahamad
R & D with IIS 5.0/6.0
modified on Thursday, June 5, 2008 1:36 AM
|
|
|
|
|
interesting, I assumed he had a normalised DB and wanted to split a very large table to improve performance.
If that is the case then I suggest you use filegroups (sql server) and have each file group on a different physical drive. The next step after that is to use partitioning to segregate your data.
Performace tuning and maintenance of a good data structure is an ART and DBAs study for years to achieve a high level of skill, you will need to put in some serious study time to get a feel for this area.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Depends on many factors, such as the amount of data, how/what you'll split up, the set up of the database, how you measure performance etc.
Generally speaking you can reduce IO if you split sensibly, take a look at the PARTITIONING of tables in SQL Server 2005 (if you use that, and other vendors might have similar functionality),which automates this a bit and which seriously can increase read performance, but can hurt write performance a little bit. Some simple tests I've done for myself showed in situations that I could get ~66% faster read simply by partitioning.
Also as suggested by a previous poster, if your tables aren't normalized, there could be a gain from simply doing that to avoid redundant data, and thus decrease the number of operations with read/write.
|
|
|
|
|
It depends. Are you talking about splitting the rows (for example having 1 table for 2008 data, 1 for 2007 etc) or splitting the columns (for example taking the address out of the customer table into an address table).
If its the former you will probably only see gains if you have very large tables, but properly partitioned there can be significant benefits where large volumes of data are involved.
If it the latter you are talking normalisation. Before leaping into too much normalisation take a look at your data useage. Normaalised data will result in joins when querying and this may have a detrimental effect on performance. For performance it is sometimes better to use denormalised data.
Its all down to volumes and use really.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I'm trying to connect a remote database (SQL Server 2000) thru internet.
I'm working on VB.NET. Also I do have a Static IP.
I'm using the following connection string.
"Data Source=<<IP ADDRESS>,1433;Initial Catalog=<db name="">;User ID=<<User ID>>;Password=<<PASSWORD>>"
But it did not work.
Can you help me with the correct connection string & other details required to access remote DB .
Thanks,
Hariram.
|
|
|
|
|
It might be some security issues or user right issues.
Can you post here exception message which you are currently getting so that we can do more analysis with issues ?
Parwej Ahamad
R & D with IIS 5.0/6.0
|
|
|
|