| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2009-04-20 : 09:54:59
|
| Hello,I creating a database that will hold localized resources such as posts, documents, contents, etc.For example, a post contains the following columns:PostID, PostTitle, PostBody, PostCreatedDate, PostUpdatedDate and PostIsPublishedLocalized Columns: PostTitle and PostBodyNO Localized columns: PostID, PostCreatedDate, PostUpdatedDate and PostIsPublishedI see two options of designing the Posts table1) Create ONE table with all columns and add a new one (Culture)2) Create TWO tables with a relationship between them: Posts > PostID, PostCreatedDate, PostUpdatedDate and PostIsPublished PostsLocalized > PostLocalizedID (PK), PostID, PostTitle, PostBody, Culture.What do you recommend?Or is there a better option for this?And should I create a Cultures table to?Thanks,Miguel |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-20 : 10:03:01
|
| I'd put it in one table. Culture can be normalized as well. |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2009-04-20 : 10:22:56
|
| But if you use only one table how can you get a post given its culture?I mean, for Post A you will have 2 records:PostID = 1, Culture = "en"PostID = 2, Culture = "pt"Both records are post A ... but how can I know that?This is what is confusing.Thanks,Miguel |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-20 : 10:32:07
|
| You could have a nullable ParentId column. So localized posts would have a parentid of the root post. |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2009-04-20 : 11:04:29
|
| Wouldn't that be kind of having the relationships in the tables itself?I know that for categories and sub categories that is used ...... the adjacent model.But when possible isn't better to use the relationships between tables? |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-20 : 11:25:48
|
| Are you sure you can't use a third-party translation service or look into globalization resources in your application layer? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-20 : 11:38:17
|
| What's wrong with sticking to a nice normalised design and having the postLocalised table.What's you current app / design like?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2009-04-20 : 11:47:24
|
quote:
Originally posted by Transact Charlie
What's wrong with sticking to a nice normalised design and having the postLocalised table.What's you current app / design like?
I am working on an ASP.NET 3.5 MVC Web Application.I am using LinqToSQL to create the database tables and relationship mapping.I then create repositories that uses Linq to get/put data from the database to/from my C# models.The data is requested using the parameters (Id and Culture).For example, Post Model contains the properties:PostID, PostTitle, PostBody, PostCreatedDate, PostUpdatedDate and PostIsPublished, CultureIs this what you meant? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-20 : 11:59:33
|
quote:
Originally posted by shapper
Hello,I see two options of designing the Posts table1) Create ONE table with all columns and add a new one (Culture)2) Create TWO tables with a relationship between them: Posts > PostID, PostCreatedDate, PostUpdatedDate and PostIsPublished PostsLocalized > PostLocalizedID (PK), PostID, PostTitle, PostBody, Culture.
Based on the information you have provided, option #2 is the correct choice. |
|
|
|
|
Localized tables