Database assistance

[Maximilian]

So ive put together more of the database for the site im building but I cant figure out how to do it. I dont really know how to word what I want to do effectively so I made this picture:

Ive got a userTable that stores a username and a password. I want to have a customFoods table that stores custom foods and nutrient data for each individual user. A separate customFoods table for each username is needed. How do I go about doing this?

 

[nickbits]

EDIT do your own home work

 

[purbeast0]

why do you need multiple customFoods tables, 1 for each user? why not store it in 1 customFoods table?

what you should do is add an index to each table. then link the users to the customFoods table via indices.

 

[Maximilian]

Its a bit more than homework lol, im building a website for my final years uni project. Im pretty sure this has something to do with a foreign key but I don't really want one huge table with everyone's custom foods and a combination of foodName+emailFK being the PK, I think it would be better to have separate tables somehow. If this is possible.

I toyed with the idea of just having one huge customFoods table with just two columns, one being email the other will contain a JSON object for each email that just stores all the food values for that email. Stuffing things into JSONs dosent seem all that elegant or flexible though, especially for a relational database.

 

[Merad]

Have you ever learned database design with entity-relationship (ER) models? It can be a bit clunky and convoluted especially on complex databases, but I find it's still very good for establishing "here's the data I need to store and the relationships between it." Once you have that diagram set up there's a pretty straightforward algorithm for converting it into a relational database.

Anyway, you only need two tables. Foods (custom foods) is a weak entity that relies on a user for its identity (a custom food is meaningless unless it is associated with a user). This means that you'll take the Pk from Users and make it both a Pk and Fk in Foods. You could use the username as the key but it would be better to give each user an id instead.

 

[brandonb]

1) Never use a name as a key. Ever. Always use a numerical number, int or bigint if you need more than 2 billion rows.

2) Going back to 1. You need an id_username, and id_food that is of datatype int which is going to be the key. This should also be an IDENTITY(1,1). So every time you insert into these tables the id will auto populate. If you can't or don't want to do this, you should be creating a CHECKSUM PERSISTED column for the foodname/username fields and searching for records based on those hashes. If you need more detail, respond and I will explain it.

	CREATE TABLE [dbo].[person]
	(

		id_person					INT PRIMARY KEY CLUSTERED IDENTITY(1,1),

		[sys]						BIT NOT NULL DEFAULT 0,
		date_added					DATETIME NOT NULL DEFAULT GETDATE(),
		date_updated				DATETIME,
		inactive					BIT NOT NULL DEFAULT 0,

		uid_def_language_type		INT FOREIGN KEY REFERENCES def_language_type(uid_def_language_type),
		uid_def_gender_type			INT FOREIGN KEY REFERENCES def_gender_type(uid_def_gender_type),
		uid_def_marital_type		INT FOREIGN KEY REFERENCES def_marital_type(uid_def_marital_type),
		uid_def_religion_type		INT FOREIGN KEY REFERENCES def_religion_type(uid_def_religion_type),
		uid_def_ethnicity_type		INT FOREIGN KEY REFERENCES def_ethnicity_type(uid_def_ethnicity_type),
	
		encrypted_taxid				VARBINARY(MAX),
		taxid_hash					INT,
				
		name_prefix					NVARCHAR(10),
		name_last					NVARCHAR(50),
		name_first					NVARCHAR(50),
		name_middle					NVARCHAR(50),
		name_suffix					NVARCHAR(10),
		name_full					AS LTRIM(RTRIM(COALESCE(name_prefix, '') + ' ' + COALESCE(name_first, '') + RTRIM(' ' + COALESCE(name_middle, '')) + RTRIM(' ' + COALESCE(name_last, '')) + ' ' + COALESCE(name_suffix, ''))),
		name_firstlast				AS LTRIM(RTRIM(COALESCE(name_first, '') + RTRIM(' ' + COALESCE(name_last, '')) + ' ' + COALESCE(name_suffix, ''))),
		name_alias					NVARCHAR(50),
		name_maiden					NVARCHAR(50),

		date_birth					DATETIME,
		date_death					DATETIME,
			
		[b]name_hash					AS CHECKSUM(COALESCE(name_last, '') + COALESCE(name_first, '') + COALESCE(name_suffix, '')) PERSISTED[/b]
	)

(that's MSSQL)

3) You need to create a mapping table to link the two tables together in a many to many relationship. Edit - You won't need this if you follow Merad's comment where you have a one to many relationship.

	CREATE TABLE [dbo].[account_detail_person]
	(

		id_account_detail_person			INT PRIMARY KEY CLUSTERED IDENTITY(1,1),

		[sys]								BIT NOT NULL DEFAULT 0,
		date_added							DATETIME NOT NULL DEFAULT GETDATE(),
		date_updated						DATETIME,
		inactive							BIT NOT NULL DEFAULT 0,

		uid_def_workflow_type				INT NOT NULL FOREIGN KEY REFERENCES def_workflow_type(uid_def_workflow_type),
		uid_def_payer						INT NOT NULL FOREIGN KEY REFERENCES def_payer(uid_def_payer),				
		[id_account_detail]					INT NOT NULL FOREIGN KEY REFERENCES account_detail(id_account_detail),
		[id_person]							INT NOT NULL FOREIGN KEY REFERENCES person(id_person),		
		[uid_def_person_type]				INT FOREIGN KEY REFERENCES def_person_type(uid_def_person_type),
		[uid_def_relation_type]				INT FOREIGN KEY REFERENCES def_relation_type(uid_def_relation_type),
				
		root_id								INT,
		previous_id							INT,
		next_id								INT,		
		
		uid_def_source_type					INT NOT NULL FOREIGN KEY REFERENCES def_source_type(uid_def_source_type),
		uid_def_source_type_sub				INT,	
		source_id							INT,				
				
		date_from							DATETIME NOT NULL DEFAULT GETDATE(),
		date_to								DATETIME		
		
	)

	CREATE NONCLUSTERED INDEX UidDefWorkflowType_UidDefPayer_IdAccountDetail ON [dbo].account_detail_person
	(uid_def_workflow_type, [id_account_detail]);

	CREATE NONCLUSTERED INDEX UidDefWorkflowType_UidDefPayer_IdPerson ON [dbo].account_detail_person
	(uid_def_workflow_type, [id_person]);

	CREATE NONCLUSTERED INDEX UidDefWorkflowType_UidDefPayer_IdAccountDetail_UidDefPersonType ON [dbo].[account_detail_person]
	(uid_def_workflow_type, uid_def_payer, [id_account_detail], [uid_def_person_type]) INCLUDE(id_person);
	
	ALTER TABLE [dbo].[account_detail_person] WITH CHECK ADD CONSTRAINT [FK__account_detail_person___root_id] FOREIGN KEY(root_id) REFERENCES [dbo].[account_detail_person] (id_account_detail_person)
	ALTER TABLE [dbo].[account_detail_person] CHECK CONSTRAINT [FK__account_detail_person___root_id]
	
	ALTER TABLE [dbo].[account_detail_person] WITH CHECK ADD CONSTRAINT [FK__account_detail_person___previous_id] FOREIGN KEY(previous_id) REFERENCES [dbo].[account_detail_person] (id_account_detail_person)
	ALTER TABLE [dbo].[account_detail_person] CHECK CONSTRAINT [FK__account_detail_person___previous_id]
	
	ALTER TABLE [dbo].[account_detail_person] WITH CHECK ADD CONSTRAINT [FK__account_detail_person___next_id] FOREIGN KEY(next_id) REFERENCES [dbo].[account_detail_person] (id_account_detail_person)
	ALTER TABLE [dbo].[account_detail_person] CHECK CONSTRAINT [FK__account_detail_person___next_id]	

	ALTER TABLE [dbo].[account_detail_person] WITH CHECK ADD CONSTRAINT [FK__account_detail_person___def_source_type_sub] FOREIGN KEY([uid_def_source_type], [uid_def_source_type_sub]) REFERENCES [dbo].def_source_type_sub ([uid_def_source_type], [uid_def_source_type_sub])
	ALTER TABLE [dbo].[account_detail_person] CHECK CONSTRAINT [FK__account_detail_person___def_source_type_sub]

This example links id_account_detail to id_person (using the table above)... Anytime a new person is assigned to the account_detail, new records are added to the account_detail_persons table.

I will let you adapt this to whatever environment you have, but if you think through this you should be able to understand.

 

[Merad]

I think it would be better to have separate tables somehow.

This isn't how relational databases work.

one huge table with everyone's custom foods and a combination of foodName+emailFK being the PK

This is how relational databases work.

I don't know about MySQL or other systems, but in MS SQL Server 2012 the theoretical limit for a database size is over 500 TB. And the size limit for a table is basically limited only by the storage on the server.

 

[purbeast0]

Its a bit more than homework lol, im building a website for my final years uni project. Im pretty sure this has something to do with a foreign key but I don't really want one huge table with everyone's custom foods and a combination of foodName+emailFK being the PK, I think it would be better to have separate tables somehow. If this is possible.

that is a terrible idea. you WANT to have just 1 table with all the custom foods in it.

and you don't need to make foodName+emailFK to be the primary key. as i mentioned, do it with indices.

 

[cabri]

Food table:
Name table:
Link table: if custom foods can be associated with multiple names or multiple foods can be associated with a given name.

 

[purbeast0]

Food table:
Name table:
Link table: if custom foods can be associated with multiple names or multiple foods can be associated with a given name.

based on his image, it appeared that each custom food is unique. however if that is not the case, then i'd definitely use the suggestion of having a link table.

 

[Maximilian]

Awesome stuff, cheers for the help guys! This is what ive got now:

So usertable now uses an id_user as a PK instead of email. It auto updates as I picked the bigserial data type which seems to be the postgresql equivalent of "IDENTITY(1,1)".

customfoodstable will be one big table with id_user as a FK and id_food as a PK.

Theres gonna be a workoutstats table as well eventually, ill do the same thing when it comes to that and just make one big table and avoid using names as keys in the future.

 

[brandonb]

Make sure you have an index in your customfoodTable on the id_user column though. Because if you ever want to search by that column (and you likely will) it will be much quicker. (Not sure if you've done this or not yet).

 

[Maximilian]

Make sure you have an index in your customfoodTable on the id_user column though. Because if you ever want to search by that column (and you likely will) it will be much quicker. (Not sure if you've done this or not yet).

Hmm I don't have an index actually. It wanted to use an index by default but I unticked that option because it kept telling me "please specify covering index name". I thought it was because "id_user" column was the same name in both tables or something. I basically put it on the figure it out later pile.

If I put an index in will it be automatically used? Or will I have to change my SQL command:

private static final String getcustomfoodsSQL = "SELECT * FROM customfoods WHERE id_user = ?";

 

[brandonb]

The database will use the index automatically once you create it, at least with MSSQL Server. MSSQL actually records statistics about indexes, and determines which index to use with which type of query behind the scenes.

Not sure about Postgres but I think it has an internal optimizer and will use the best index for the query too. I think the feature set between MSSQL and Postgres for normal db queries is pretty similar.

 

[Maximilian]

Ah that sounds good. I had a sift through the documentation (I gotta learn to do this more often) and it says "Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will use the index in queries when it thinks doing so would be more efficient than a sequential table scan." So yeah seems like a set and forget kinda thing.

http://www.postgresql.org/docs/9.1/static/indexes-intro.html

Cheers for the help brandon!

 

[BoberFett]

At the risk of sounding insulting...

I'm curious, this is for your final project, but it seems like your school hasn't taught you anything about database design. What did you study?

 

[Maximilian]

At the risk of sounding insulting...

I'm curious, this is for your final project, but it seems like your school hasn't taught you anything about database design. What did you study?

Im doing an Open University BSc. "Q62 Computing & IT" Theres different specializations to pick networking, computer science etc, I picked the software development specialization.

The degree is made up of a bunch of modules. Sometimes you get a choice with modules, other times modules are mandatory. Last year I had a choice between "M359 Relational databases: theory and practice" and "M364 Interaction Design" Guess which one I picked :$

I read some reviews saying the database module wasent very practical and was mainly theory based which put me off. I learned a little about databases during "M263 Building blocks of software" but that was ~3 years ago. The primary key/foreign key thing is all I remember from that.

 

[ph2000]

There is few situation where you need to touch index, but i dont think you would need to worry about it for the taak at hand.